Delete duplicate rows

Language: English
Programming Language: SQL
Published by: haighis [not registered]
Last Update: 5/15/2006
Views: 1400

Description

This will delete duplicate rows in a table. Excellent maintenance script.

Code

Display line numbers

1 DELETE 2 FROM Foo1 3 WHERE Foo1.ID IN 4 5 -- List 1 - all rows that have duplicates 6 (SELECT F.ID 7 FROM Foo1 AS F 8 WHERE Exists (SELECT Field1, Field2, Count(ID) 9 FROM Foo1 10 WHERE Foo1.Field1 = F.Field1 11 AND Foo1.Field2 = F.Field2 12 GROUP BY Foo1.Field1, Foo1.Field2 13 HAVING Count(Foo1.ID) > 1)) 14 AND Foo1.ID NOT IN 15 16 -- List 2 - one row from each set of duplicate 17 (SELECT Min(ID) 18 FROM Foo1 AS F 19 WHERE Exists (SELECT Field1, Field2, Count(ID) 20 FROM Foo1 21 WHERE Foo1.Field1 = F.Field1 22 AND Foo1.Field2 = F.Field2 23 GROUP BY Foo1.Field1, Foo1.Field2 24 HAVING Count(Foo1.ID) > 1) 25 GROUP BY Field1, Field2);

Delete duplicate rows

Description

Code

No comments avaiable

Add a comment