Delete duplicate rows

Sprache: English
Programmiersprache: SQL
Veröffentlicht von: haighis [nicht registriert]
Letzte Änderung: 15.05.2006
Aufrufe: 1466

Lizenz: GNU General Public License

Beschreibung

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

Code

Zeilennummern anzeigen

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);

Noch kein Kommentar vorhanden

Dieses Snippet kommentieren

Name *

E-Mail (wird nicht angezeigt) *

Website

Kommentar *

Sicherheitscode *