SQL – Duplikate finden und löschen

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.

Das könnte Dich auch interessieren …

23 Antworten

  1. Mikey sagt:

    Great Stuff !! Helped alot, thank you 🙂

  2. Bratfist sagt:

    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)

  3. Dan sagt:

    „Leider funktioniert dass,….“

    …Funktioniert nicht:

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

  4. dwolke sagt:

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

  5. Timo sagt:

    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“

  6. Timo sagt:

    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!

  7. Rene Unterbachleitner sagt:

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

  8. abcman sagt:

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

  9. Johannes sagt:

    Super, klappt einwandfrei. !!!! 🙂

  10. Pascal sagt:

    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;

  11. dwolke sagt:

    Welche MySQL-Version benutzt Du?

  12. KF sagt:

    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.

  13. Stefan Bradler sagt:

    Perfekt !
    Besser als having count

  14. MK sagt:

    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 🙂

  15. Sven sagt:

    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

  16. Alex sagt:

    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.

  17. DocRattie sagt:

    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.

  18. Sean sagt:

    Vielen Dank! Das hat mir unglaublich geholfen 🙂

  19. JWP sagt:

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

  20. Hartmut sagt:

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

  21. Joe sagt:

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

  22. Elija sagt:

    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!

  23. D. Lepold sagt:

    Ich strauchelte ein wenig an der DELETE FROM … USING … WHERE Lösung,… Da ich es erst testen wollte, versuchte ich es mit SELECT FROM … USING… WHERE …
    und das klappte nicht (Fehler), …
    Dann wollte ich es mit 10 Einträgen/Löschungen testen,
    indem ich am Ende LIMIT 10 dranhängte,
    ging nicht: Fehler.
    Dann lies ich das LIMIT weg, und: es funktionierte:
    „1867 rows deleted. (Query took 1.3025 seconds.)“

    Query war:

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

    Das klappte 🙂

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.