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.

22 Antworten auf „SQL – Duplikate finden und löschen“

  1. 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)

  2. „Leider funktioniert dass,….“

    …Funktioniert nicht:

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

  3. 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“

  4. 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!

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

  6. 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;

  7. 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.

  8. 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 🙂

  9. 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

  10. 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.

  11. 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.

  12. 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!

Schreibe einen Kommentar

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