rss
twitter
  •  

SQL – Duplikate finden und löschen

| Posted in Technik |

22

Hin und wieder ist man mit doppelten Einträgen in einer Datenbank und dem Problem der Beseitigung konfrontiert. Nachfolgend zeige ich einen Weg, wie sich per SQL Duplikate effektiv finden und entfernen lassen.

Meine Test-Tabelle ist eine einfache Adresstabelle mit etwas über 400.000 Datensätzen und etlichen Duplikaten.

Der übliche Tipp zum Auffinden von Duplikaten mit Hilfe von GROUP BY und HAVING COUNT(*)>1 ist aber in meinen Augen sehr unpraktisch, wie die folgende Abbildung verdeutlicht.

Duplikate finden - Variante 1

Nicht wirklich praktikabel…

Viel anzufangen ist mit diesem Ergebnis nicht. Wir wissen jetzt zwar, daß wir doppelte Einträge in der Tabelle haben, aber das wußten wir vorher ja auch schon. Ein weiterer Nachteil dieser Abfrage ist die schlechte Performance im Vergleich zur nachfolgend vorgestellten Lösung.

Die wesentlich elegantere Variante ist die Verwendung einer sogenannten Correlated Subquery. Hinter dieser Art der Abfrage verbirgt sich ein indirekter Self-Join, da ein und dieselbe Tabelle in zwei miteinander in Wechselwirkung stehenden Abfragen gemeinsam verwendet wird. Über eine Correlated Subquery kann ich nun also alle doppelten (oder auch mehrfach vorkommenden) Datensätze mit jeweils einer einzigen SQL-Anweisung anzeigen bzw. löschen.

Mit der folgenden Abfrage lasse ich mir alle Datensätze anzeigen, in denen Vorwahl und Telefonnummer doppelt vorkommen.

SELECT Id, Vorname, Nachname, Strasse, PLZ, Ort, Vorwahl, Telefon
FROM testdat
WHERE EXISTS (
SELECT Id FROM testdat Dup WHERE testdat.Vorwahl = Dup.Vorwahl AND testdat.Telefon = Dup.Telefon AND testdat.Id <> Dup.Id)
ORDER BY Nachname;

Wie die nächste Abbildung zeigt, sind die gelieferten Informationen hier wesentlich ausführlicher.

aa

…schon besser!

Auf gleiche Weise kann ich nun auch die vorhandenen Duplikate aus der Tabelle löschen:

DELETE FROM testdat
WHERE EXISTS (
SELECT Id FROM testdat Dup WHERE testdat.Vorwahl = Dup.Vorwahl AND testdat.Telefon = Dup.Telefon AND testdat.Id < Dup.Id);

Damit werden nun alle Duplikate mit der kleineren Id gelöscht.

Comments (22)

Great Stuff !! Helped alot, thank you 🙂

Leider funktioniert dass, wenn nur Duplikate vorkommen. Kommt ein Datensatz drei- oder mehrmals vor, funktioniert dieses Verfahren nicht, da die EXSISTS-Abfrage mehr als einen Datensatz liefert.
Das würde dann so gehen:
DELETE FROM testdat WHERE testdat.ID In (SELECT t1.ID FROM testdat T1,testdat T2 WHERE T1.Vorwahl = T1.Vorwahl AND … AND T1.id>T2.id)

„Leider funktioniert dass,….“

…Funktioniert nicht:

#1093 – You can’t specify target table ‚testdat‘ for update in FROM clause

Poste doch mal, was du genau machen willst und deine komplette Abfrage, vielleicht finden wir den Fehler. 😉

Hallo,

danke für die Anleitung, klappt bei mir schon zur Hälfte:)

Die Delete Anweisung bringt bei mir aber folgende Meldung:

„#1093 – You can’t specify target table ‚test‘ for update in FROM clause“

Hallo,

danke für die Anleitung, klappt bei mir schon zur Hälfte:)

Die Delete Anweisung bringt bei mir aber folgende Meldung:

„#1093 – You can’t specify target table ‚test‘ for update in FROM clause“

Was ist da falsch? Gruss!

Vielen Dank für die einfache und übersichtliche Lösung.
Funktioniert perfekt!

Kann man es auch so einrichten, dass in der Abfrage keine Dubletten angezeigt werden, aber nichts gelöscht wird?

Super, klappt einwandfrei. !!!! 🙂

Also bei mir funktioniert die SELECT Anweisung einwandfrei -> Super Lösung, vielen Dank!

Allerdings kann ich die DELETE Anweisung so nicht ausführen, auch ich bekomme folgende Fehlermeldung:

“#1093 – You can’t specify target table ‘test’ for update in FROM clause”

Meine SQL-Codes:
— SELECT Anweisung (funktioniert)
SELECT verID,verDAT_START,verTIME_START,verTITEL
FROM TEST
WHERE EXISTS (
SELECT verID FROM TEST Dup
WHERE TEST.verDAT_START=Dup.verDAT_START
AND TEST.verTIME_START=Dup.verTIME_START
AND TEST.verTITEL=Dup.verTITEL
AND TEST.verIDDup.verID)
ORDER BY verDAT_START;

— DELETE Anweisung (funktioniert nicht)
DELETE FROM TEST
WHERE EXISTS (
SELECT verID FROM TEST Dup
WHERE TEST.verDAT_START=Dup.verDAT_START
AND TEST.verTIME_START=Dup.verTIME_START
AND TEST.verTITEL=Dup.verTITEL
AND TEST.verID<Dup.verID)
ORDER BY verDAT_START;

Welche MySQL-Version benutzt Du?

Den Fehler bei der DELETE-Anweisung kann man umgehen, indem man von der Tabelle eine Kopie erstellt und die Unterabfrage (alles mit „Dup.“) von dort auslesen lässt.

Perfekt !
Besser als having count

Bei einem Delete mit Subquery muss man das eigentliche SELECT der Subquery nochmal explizit angeben. Heißt aus einem

DELETE FROM testdat
WHERE EXISTS (
SELECT Id FROM testdat Dup WHERE testdat.Vorwahl = Dup.Vorwahl AND testdat.Telefon = Dup.Telefon AND testdat.Id < Dup.Id);

wird

DELETE FROM testdat
FROM testdat
WHERE EXISTS (
SELECT Id FROM testdat Dup WHERE testdat.Vorwahl = Dup.Vorwahl AND testdat.Telefon = Dup.Telefon AND testdat.Id < Dup.Id);

Dann sollte das DELETE bei euch auch funktionieren 🙂

Sorry, hat bei mir nicht funktioniert. Dafür dies hier:

DELETE FROM testdat
USING testdat, testdat as Dup
WHERE NOT testdat.id = Dup.id
AND testdat.id > Dup.id
AND testdat.email = Dup.email

Hallo zusammen, ich suche nach einer Lösung für mein Problem. Es gibt Listenansicht und Kartenansicht von Ferienwohnungen.

Bei Listenansicht ist alles gut. Alle Datensätze werden angezeigt.

Bei Kartenansicht -> 2 Ferienwohnungen unter gleicher Adresse. Wie bekomme ich es hin, dass man auf der Karte auf das Icon klickt und es werden nur die beiden Ferienwohnungen angezeigt, die unter jeweiliger gleicher Adresse vorkommen.

Für den DELETE sollte man folgendes verwenden:

DELETE FROM testdat
USING testdat, testdat Dup
WHERE …

Das geht zwar im SELECT nicht, aber der DELETE geht nur damit.

Vielen Dank! Das hat mir unglaublich geholfen 🙂

Perfekt – Das Beispiel lies sich auf Anhieb umsetzen, Ergebnis passt

Vielen Dank! Ihr habt mich gerettet!
Das Beisspiel passt sowas von…..

Feine Sache
Perfekt – Das Beispiel lies sich auf Anhieb umsetzen, Ergebnis passt

Danke für die Anleitung.

Gute Idee, funktioniert aber halt nur, wenn du eine separate ID in der Tabelle hast und keine reine Dupletten, bei denen wirklich die ganze Zeile genau gleich ist.

Danke!

Post a comment