Doppelte Einträge aus der Datenbank löschen

16 Kommentare
Mischa Sameli, Geschäftsführer & Leiter Entwicklung

Wie entfernt man mit einer Datenbankabfrage doppelte Einträge in einer Tabelle, ohne temporäre Tabellen erstellen zu müssen? Hier ein Beispiel für drei populäre Datenbanksysteme, die sich bei uns im Einsatz befinden: MSSQL, MySQL und Postgres.

Ums gleich vorweg zu nehmen: Natürlich ist es besser, doppelte Einträge gar nicht erst entstehen lassen. Beispielsweise durch Überprüfen der einzutragenden Daten bevor sie eben den Weg in die Datenbank finden. Leider wird das nicht immer respektive nicht immer konsequent gemacht und so stösst man ab und zu auf dieses Problem.

Als Beispiel nehmen wir eine einfache Tabelle comment, die folgende Spalten enthält:

  • id
  • name
  • email
  • message

Die Tabelle kann enthält, wie der Name bereits verrät, möglichweise Daten eines simplen Kontaktformulars oder eine Gästebuchs. Doppelte Einträge entstehen dabei leicht, wenn beispielsweise das Formular mehrfach abgesendet werden kann (Gruss an alle Doppelklicker im Internet) oder durch Formular-Spam, der nicht entdeckt und gefiltert wird.

Mit einem einzigen Query können nun bei PostgreSQL und Microsofts SQL-Server Duplikate entfernt werden:

1DELETE FROM comment
2WHERE id IN (
3    SELECT id FROM comment
4        WHERE EXISTS(
5            SELECT NULL FROM comment AS tmpcomment
6            WHERE comment.email = tmpcomment.email
7            AND comment.name = tmpcomment.name
8            AND comment.message = tmpcomment.message
9            HAVING comment.id > MIN(tmpcomment.id)
10            )
11    )

Für Postgres funktioniert grundsätzlich die gleiche Datenbankabfrage, aber ein wenig optimaler ist folgendes pgsql Script. Das Statement berücksichtigt mehrere Sync-Spalten und nicht nur doppelte IDs.

1DELETE FROM comment
2WHERE id IN SELECTT id
3    FROM SELECTT id,
4        row_number() over (partition BY spalte1, spalte2, spalteX ORDER BY id) AS rnum
5            FROM comment) t
6    WHERE t.rnum > 1);

Noch spezieller und vor allem effizienter funktioniert die Abfrage mit dem USING Befehl von Postgres:

1DELETE FROM commets USING comment comment2
2    WHERE comment.email = comment2.email AND comment.id < comment2.id;

Für MySQL (getestet mit der 5er-Version) sieht die Abfrage ein wenig anders aus:

1DELETE FROM comment
2USING comment, comment as tmpcomment
3WHERE NOT comment.id=tmpcomment.id
4    AND comment.id > tmpcomment.id
5    AND comment.name=tmpcomment.name
6    AND comment.email=tmpcomment.email
7    AND comment.message=tmpcomment.message

neuen Kommentar erstellen

Marco Betschart's Gravatar
Da hat sich ein kleiner Typo in der ersten Abfrage in Zeile 3 eingeschlichen.

Ansonsten vielen Dank für den Tipp!

PS: Die ORM Variante wäre ebenfalls interessant ;)
Marco Betschart, am 8. Oktober 2010 um 10:03 Uhr
Mischa Sameli's Gravatar
Hoppla, der Fehler wird durch die Syntax-Highlightung generiert. Bei Zeile 2 verschwindet auch eine Klammer - gehe der Sache gerne nach. Danke für den Hinweis.

Die ORM-Variante für Hibernater mit HQL dürfte eigentlich auch keine Hexerei sein. Wird ebenfalls gerne nachgereicht. Danke für den Tipp.
Mischa Sameli, am 8. Oktober 2010 um 10:22 Uhr
Mischa Sameli's Gravatar
Soviel zum Thema: dürfte kein Problem sein.
Hibernate biete derzeit keine Möglichkeit, solche Delete-Statements zu formulieren. Dazu müsste man direkt die Hibernate-Session aufgreifen und die JDBC-Verbindung zur Datenbank benützen. Die Queries oben könnten dann wiederum verwendet werden.

Die komplette Erläuterung dazu findet man in der Dokumentation von Hibernate: http://docs.jboss.org/hibernate/stable/core/refere...
Mischa Sameli, am 8. Oktober 2010 um 15:15 Uhr
Marco Betschart's Gravatar
So etwas habe ich fast befürchtet. Schade eigentlich. Mal sehen was die Zukunft bringt und auf jedenfall vielen Dank für deine Nachforschungen!

Wünsch euch allen ein schönes Wochenende!
Marco Betschart, am 8. Oktober 2010 um 15:29 Uhr
Mischa Sameli's Gravatar
In Zukunft solls ja schon möglich sein - lassen wir uns mal überraschen.

Die gleiche Problematik gilt ja auch für Batch-Inserts oder Updates - auch da muss ein Umweg in Kauf genommen werden, wie wir diese Woche festgestellt haben. Unschön.
Mischa Sameli, am 8. Oktober 2010 um 15:40 Uhr
Claudio Götz's Gravatar
Guten Tag miteinander!

Bei der SQL-Query werden alle mehrfach vorhandenen einträge gelöscht und nicht nur die duplikate davon.. oder mache ich etwas falsch?

Freundliche Grüsse und Danke für die Antwort
Claudio Götz, am 28. Februar 2012 um 23:04 Uhr
Mischa Sameli's Gravatar
Welche der beiden Versionen hast Du denn ausprobiert?
Mischa Sameli, am 29. Februar 2012 um 14:44 Uhr
Claudio Götz's Gravatar
1DELETE FROM comment
2USING comment, comment as tmpcomment
3WHERE NOT comment.id=tmpcomment.id
4 AND comment.id > tmpcomment.id
5 AND comment.name=tmpcomment.name
6 AND comment.email=tmpcomment.email
7 AND comment.message=tmpcomment.message

der deletet mir zwar alle doppelten aber behält mir keinen^^ das ist ja auch nicht der sinn der sache..
Claudio Götz, am 29. Februar 2012 um 15:29 Uhr
Mischa Sameli's Gravatar
Das Beispiel funktioniert bei mir mit der oben genannten Tabellenstruktur auf MySQL 5.5 tadellos. Meine Beispiel-Daten für die Tabelle:
1, Mischa, m.sameli@backslash.ch, test1
2, Markus, news.sameli@backslash.ch, test2
3, Mischa, m.sameli@backslash.ch, test1

Gelöscht wird dabei nur der Eintrag 3. Kann es an der MySQL-Version liegen oder sind Primarys nicht gesetzt?

Hast Du vielleicht ein Beispiel Deiner effektiven Daten?
Mischa Sameli, am 29. Februar 2012 um 15:44 Uhr
Claudio Götz's Gravatar
id | phone | name | street
1 | 041 587 78 78 | Claudio | Musterstrasse 3
2 | 041 123 45 45 | Hans | Teststrasse 8
3 | 041 587 78 78 | Claudio | Musterstrasse 3

primary ist die id, ebenfalls indexiert aber das sollte ja keinen unterschied machen..
Claudio Götz, am 29. Februar 2012 um 16:29 Uhr
Mischa Sameli's Gravatar
Auch das funktioniert bei mit tadellos mit folgendem Query:
DELETE FROM adresse
USING adresse, adresse as tmpadresse
WHERE NOT adresse.id=tmpadresse.id
AND adresse.id > tmpadresse.id
AND adresse.phone=tmpadresse.phone
AND adresse.name=tmpadresse.name
AND adresse.street=tmpadresse.street
Mischa Sameli, am 29. Februar 2012 um 16:36 Uhr
Claudio Götz's Gravatar
Super! Habs gerade getestet und funktioniert perfekt!
Danke für die schnelle Antwort und die fehlerfreie Query!
Claudio Götz, am 29. Februar 2012 um 19:17 Uhr
Robert Kitzmann's Gravatar
Wunderbar Herr Sameli, top ich bin begeistert! Das Lexikon www.langua.de Dankt und hat nun mehr Übersichtlichkeit!
Robert Kitzmann, am 20. März 2013 um 13:21 Uhr
anonym's Gravatar
Super.
Es funktioniert einwandfrei.
Vielen Dank
anonym, am 10. Dezember 2014 um 10:12 Uhr
Fiete's Gravatar
Half auch mir - Vielen Dank.
Fiete, am 12. Juni 2015 um 15:38 Uhr
Bitte lassen Sie dieses Feld leer
Kommentar hinzufügen


Bitte lassen Sie dieses Feld leer
Beitrag als E-Mail verschicken
E-Mail via Webmail versenden

Schön, dass Ihnen unser Beitrag gefallen hat. Benutzen Sie folgende Social Networking Dienste, um den Beitrag abzulegen und zu verteilen. Selbstverstädlich können Sie ein direktes Lesezeichen auf diesen Artikel setzen.