Deleting one of duplicate rows

[shilpa_lively]

Hi can any one please help me in the SQL query for deleting only one of the duplicate rows(if i have 2 duplicate rows)

thanks
shilpa

[Shuja Ali]

Hi can any one please help me in the SQL query for deleting only one of the duplicate rows(if i have 2 duplicate rows)

thanks
shilpa
Try this Link

http://www.sql-server-performance.com/dv_delete_duplicates.asp

[shilpa_lively]

can't we do it using nested sub queries n stuff
Thanks
shilpa

[srinika]

Put all into a Temp Table as follows;

[Assume the table contains the fields F1 & F2]

Select * into T_NoDup from T_Dup Group by F1, F2

Delete the content of original table and insert the content of Temp table to the original. Drop the temp table.

Delete from T_Dup
Insert Into T_Dup Select * from T_NoDup
Drop table T_NoDup

[shilpa_lively]

but i want to delete only one of the duplicate rows(if i have 2 duplicaterows with the same information and i needto delete only second record)then????

[Zeb]

How about this:DELETE
FROM Foo1
WHERE Foo1.ID IN

-- List 1 - all rows that have duplicates
(SELECT F.ID
FROM Foo1 AS F
WHERE Exists (SELECT Field1, Field2, Count(ID)
FROM Foo1
WHERE Foo1.Field1 = F.Field1
AND Foo1.Field2 = F.Field2
GROUP BY Foo1.Field1, Foo1.Field2
HAVING Count(Foo1.ID) > 1))
AND Foo1.ID NOT IN

-- List 2 - one row from each set of duplicate
(SELECT Min(ID)
FROM Foo1 AS F
WHERE Exists (SELECT Field1, Field2, Count(ID)
FROM Foo1
WHERE Foo1.Field1 = F.Field1
AND Foo1.Field2 = F.Field2
GROUP BY Foo1.Field1, Foo1.Field2
HAVING Count(Foo1.ID) > 1)
GROUP BY Field1, Field2)You will need to make sure though that each row has a unique ID (which you could probably add to the table before you run the query if one doesn't exist already). If it doesn't have a unique ID, and you can't add one temporarily, then I think you will need follow vb_the_bests advice, or try another method (i.e. not nested queries, but cursors or something)

[srinika]

but i want to delete only one of the duplicate rows(if i have 2 duplicaterows with the same information and i needto delete only second record)then????

Need some info!!

1. If u have more than 2 duplicates, howmany u want to delete?
2. If the 2 duplicates are identical what do u mean by "Second Record" ? Isn't it any one out of the 2 records ?

If u want to delete all duplicates except one, and keep all the non duplicating stuff, my method works fine.

Srinika