Delete duplicate rows from table expect one row

Posted by Kundan64 under Sql Server on 1/8/2013 | Points: 10 | Views : 1512 | Status : [Member] | Replies : 7

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

I have table CityInfo like:

City State
NewDelhi Delhi
NewDelhi Delhi
Gurgaon HR
Noida UP
Noida UP

I want the Query for delete the Duplicate City from the table, but one record must be available in table, after deletion table record should be like:

City State
NewDelhi Delhi
Gurgaon HR
Noida UP

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Amaren1982 on: 1/8/2013 [Member] Starter | Points: 25

0	create table CityMaste ( ID int identity(1,1), City varchar(200), State varchar(200) ) insert into CityMaste (city,State) values ('New Delhi','Delhi') insert into CityMaste (city,State) values ('New Delhi','Delhi') insert into CityMaste (city,State) values ('Gurgaon','HR') insert into CityMaste (city,State) values ('Noida','UP') insert into CityMaste (city,State) values ('Noida','UP') 1st method delete from CityMaste where ID not in ( select max(ID) from CityMaste group by city) 2nd method delete FROM CityMaste WHERE CityMaste.%%physloc%% NOT IN (SELECT MIN(cm.%%physloc%%) FROM CityMaste cm GROUP BY cm.city) Warm Regards, AMARENDRA KUMAR AMAR SSE 9990662544 amaren1982@gmail.com Kundan64, if this helps please login to Mark As Answer. \| Alert Moderator

create table CityMaste
(
ID int identity(1,1),
City varchar(200),
State varchar(200)
)

insert into CityMaste (city,State) values ('New Delhi','Delhi')
insert into CityMaste (city,State) values ('New Delhi','Delhi')
insert into CityMaste (city,State) values ('Gurgaon','HR')
insert into CityMaste (city,State) values ('Noida','UP')
insert into CityMaste (city,State) values ('Noida','UP')

1st method
delete from CityMaste where ID not in ( select max(ID) from CityMaste group by city)

2nd method
delete FROM CityMaste WHERE CityMaste.%%physloc%% NOT IN (SELECT MIN(cm.%%physloc%%) FROM CityMaste cm GROUP BY cm.city)

Warm Regards,
AMARENDRA KUMAR AMAR
SSE
9990662544
amaren1982@gmail.com

Kundan64, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Saratvaddilli on: 1/8/2013 [Member] [MVP] Bronze | Points: 25

0	Do one thing Add an identity column Perform this action DELETE FROM MyTable WHERE ID NOT IN ( SELECT MAX(ID) FROM MyTable GROUP BY DuplicateColumn1, DuplicateColumn2) Drop the identity column --------OR------------- Select the distinct records create a temp table , drop the original table and rename the temp table to original table Thanks and Regards V.SaratChand Show difficulties that how difficult you are Kundan64, if this helps please login to Mark As Answer. \| Alert Moderator

Do one thing

Add an identity column
Perform this action

DELETE

FROM MyTable

WHERE ID NOT IN

(

SELECT MAX(ID)

FROM MyTable

GROUP BY DuplicateColumn1, DuplicateColumn2)

Drop the identity column

--------OR-------------

Select the distinct records create a temp table , drop the original table and rename the temp table to original table

Thanks and Regards
V.SaratChand
Show difficulties that how difficult you are

Kundan64, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Raja_89 on: 1/9/2013 [Member] Starter | Points: 25

0	Hai Thanks dude Is it possible to get like std1000 Regards Kundan64, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Saratvaddilli on: 1/9/2013 [Member] [MVP] Bronze | Points: 25

0	hi raja, can you please make your question clear Thanks and Regards V.SaratChand Show difficulties that how difficult you are Kundan64, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Raja_89 on: 1/9/2013 [Member] Starter | Points: 25

0	Hai Dude You can achieve this by %%lockres %% DELETE FROM dummytable WHERE dummytable.%%lockres%% NOT IN (SELECT MIN(b.%%lockres%%) FROM dummytable b GROUP BY b.city_info) Kundan64, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Raja_89 on: 1/9/2013 [Member] Starter | Points: 25

0	Hai Dude If i click the button i want to generate "std1000" to std10001 untill max value [ASP.NET C#] Kundan64, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Kundan64 on: 1/10/2013 [Member] Starter | Points: 25

0	Is this possible without use of Identity column? Kundan64, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts