Aus einer Tabelle sollen mehrfach vorkommende Datensätze ermittelt und gelöscht werden

• » Home
• »SQL-Praxis
• » Doppelte Datensätze löschen

• Sie stellen fest, daß eine Ihrer Tabellen mehrfach vorkommende Datensätze enthält. Willkürlich soll einer dieser Datensätze ausgewählt und alle anderen gelöscht werden. Wie läßt sich dies mit einer Sql-Anweisung erreichen?
• Betrachten Sie als Beispiel eine Tabelle mit vier Spalten. Die erste Spalte enthält eine Id, die anderen Spalten enhalten Inhalt.

  CREATE TABLE tbl_Personen([Personen-Id] INT
  	IDENTITY(1, 1) PRIMARY KEY,
  	Nachname VARCHAR(50),
  	Vorname VARCHAR(50),
  	Geburtstag DATETIME)
  
  
  INSERT INTO tbl_Personen(
  	Nachname, Vorname, Geburtstag)
  
  SELECT 'Schmidt', 'Hans', '1.1.1950'
  UNION SELECT 'Schmidt', 'Hans', '1.1.1950'
  UNION SELECT 'Schmidt', 'Hans', '1.1.1950'
  UNION SELECT 'Maier', 'Franz', NULL
  UNION SELECT 'Maier', 'Franz', NULL
  UNION SELECT 'Schubert', 'Gerd', '1.7.1960'

  Kopieren Sie sich den Code in eine Datei oder speichern Sie sich, falls Sie den MS-Sqlserver verwenden, die Datei Doppelte-Daten-Zeilen-loeschen.sql. Das Script erstellt zusätzlich eine gespeicherte Prozedur 'up_zeige_Zeilen_zum_Loeschen', welche die unten erläuterte SELECT-Anweisung enthält.

Doppelte Zeilen identifizieren

Mit einem Sql-Befehl, der alle Spalten mit Ausnahme der Identitätsspalte enthält, ermitteln Sie, welche Datensätze mehrfach eingetragen sind.

SELECT A.Nachname, A.Vorname, A.Geburtstag
FROM tbl_Personen As A
GROUP BY A.Nachname, A.Vorname, A.Geburtstag
HAVING COUNT(*) > 1

Ermittlung aller Id's dieser mehrfach vorhandenen Datensätze

Möchten Sie diese Datensätze ansehen, so schließen Sie mit einer Unterabfrage alle Datensätze aus, die nur einmal vorkommen. Dies erreichen Sie, indem Sie in der obigen HAVING-COUNT - Klausel auf = 1 prüfen und die hierdurch erhaltenen ID's mit NOT IN als Ausschlußkriterium verwenden.

SELECT A.[Personen-Id] As [Datensatz-ist-Kandidat-zum-Loeschen],
	A.Nachname, A.Vorname, A.Geburtstag

FROM tbl_Personen As A

Where A.[Personen-Id] NOT IN

	(SELECT MIN(X.[Personen-Id]) As [Datensatz-ist-einmalig]
		FROM tbl_Personen As X
		GROUP BY X.Nachname, X.Vorname, X.Geburtstag
		HAVING COUNT(*) = 1)

Diese Abfrage dürfen Sie jedoch nicht zum tatsächlichen Löschen verwenden. Denn dann würden sämtliche mehrfach auftretenden Datensätze gelöscht, anstatt daß genau ein Datensatz erhalten bleibt. Beachten Sie, daß es aufgrund der Gruppierung notwendig ist, über X.[Personen-Id] zu aggregieren. Da die HAVING-Klausel ohnehin nur einen Datensatz liefert, ist die Wahl der Aggregatfunktion eher willkürlich.

Einschub: INNER JOIN mit einer Unterabfrage

Betrachten Sie die folgende Abfrage:

SELECT A.[Personen-Id] As [Datensatz-ist-Kandidat-zum-Loeschen]

	From tbl_Personen As A INNER JOIN

		(Select X.Nachname, X.Vorname, X.Geburtstag
			From tbl_Personen As X
			Group By X.Nachname, X.Vorname, X.Geburtstag
			Having Count(*) > 1) As B

	On 	A.Nachname = B.Nachname
		And A.Vorname = B.Vorname
		And A.Geburtstag = B.Geburtstag

Die Unterabfrage ermittelt alle Mengen mehrfach vorkommender Zeilen. Mit dem INNER JOIN wird diese reduzierte Tabelle mit der Haupttabelle verknüpft, so daß von dieser alle zu den mehrfach vorkommenden Zeilen gehörenden ID's aufgelistet werden.

Nur: Diese Lösung funktioniert nur dann, falls alle Zellen belegt sind. Gibt es mehrfache Zeilen, bei welchen eine Zelle leer ist, so werden diese durch den INNER JOIN ausgeschlossen. Sie sehen diesen Effekt in der Beispieltabelle, da nur die ID's 1 - 3 ausgegeben werden, der Datensatz 'Maier', 'Franz', NULL jedoch fehlt.

Verwendet man stattdessen den LEFT JOIN, so werden auch alle Zeilen mit hinzugenommen, für die es in der rechten Zeile keinen übereinstimmenden Datensatz gibt, insbesondere auch all jene Zeilen, die nur einmal vorkommen. Die Version mit LEFT JOIN zeigt Ihnen also alle Datensätze von tbl_Personen an, durch den LEFT JOIN wurde die Filterung wieder aufgehoben.

Ermittlung einer zufälligen Id der eventuell mehrfach vorhandenen Datensätze

Da Sie nicht sämtliche mehrfach vorhandenen Datensätze löschen möchten, sondern einen dieser zu behalten wünschen, können Sie willkürlich einen Datensatz pro übereinstimmender Datenmenge auswählen. Dies kann bsp. durch das Minimum der ID's erreicht werden.

SELECT MIN(A.[Personen-Id]) As [Datensatz-soll-erhalten-bleiben]

FROM tbl_Personen As A

GROUP BY A.Nachname, A.Vorname, A.Geburtstag

Hier könnte man eine Unterabfrage einfügen, so daß nur jene Zeilen berücksichtigt werden, die tatsächlich mehrfach existieren. Die Verknüpfung mit der äußeren Tabelle würde jedoch erneut das oben erwähnte Problem mit den leeren Zellen erzeugen, so daß der folgende LEFT JOIN dasselbe Ergebnis liefert wie die sehr viel kürzere obige Abfrage:

SELECT MIN(A.[Personen-Id]) As [Datensatz-soll-erhalten-bleiben]

FROM tbl_Personen As A LEFT JOIN

	(SELECT X.Nachname, X.Vorname, X.Geburtstag
		FROM tbl_Personen As X
		GROUP BY X.Nachname, X.Vorname, X.Geburtstag
		HAVING COUNT(*) > 1) As B

ON 	A.Nachname = B.Nachname
	AND A.Vorname = B.Vorname
	AND A.Geburtstag = B.Geburtstag

GROUP BY A.Nachname, A.Vorname, A.Geburtstag

Ermittlung der tatsächlich zu löschenden Zeilen

Sind alle Zellen belegt, erhalten Sie mit INNER JOIN alle mehrfachen Zeilen, mit der MIN(Id) wählen Sie hiervon eine Zeile aus. Damit kann der eine Ausdruck als Einschließungs-, der andere Ausdruck als Ausschließungskriterium verwendet werden:

SELECT A.[Personen-Id], A.Nachname, A.Vorname, A.Geburtstag

FROM tbl_Personen As A

WHERE [Personen-Id] IN
	(SELECT A.[Personen-Id] As [Datensatz-ist-Kandidat-zum-Loeschen]

	From tbl_Personen As A INNER JOIN

		(Select X.Nachname, X.Vorname, X.Geburtstag
			From tbl_Personen As X
			Group By X.Nachname, X.Vorname, X.Geburtstag
			Having Count(*) > 1) As B

	On 	A.Nachname = B.Nachname
		And A.Vorname = B.Vorname
		And A.Geburtstag = B.Geburtstag)


And [Personen-Id] Not IN

	(SELECT Min(A.[Personen-Id]) As [Datensatz-soll-erhalten-bleiben]

	From tbl_Personen As A INNER Join

		(Select X.Nachname, X.Vorname, X.Geburtstag
			From tbl_Personen As X
			Group By X.Nachname, X.Vorname, X.Geburtstag
			Having Count(*) > 1) As B

	On 	A.Nachname = B.Nachname
		And A.Vorname = B.Vorname
		And A.Geburtstag = B.Geburtstag


	Group By A.Nachname, A.Vorname, A.Geburtstag)

Wie Sie an den Beispieldaten sehen, funktioniert diese Lösung nicht mehr, falls Zellen leer sind. Es werden nur die beiden Zeilen für 'Schmidt', 'Hans', '1.1.1950' mit den ID's 2 und 3 zurückgegeben, es fehlt die ebenfalls zu löschende ID 5 mit 'Maier', 'Franz', NULL.

Ein Wechsel zum LEFT JOIN liefert Ihnen für die einschließende Unterabfrage alle Zeilen zurück, dieser Teil kann entfernt werden. Die zweite Unterabfrage liefert - mit LEFT JOIN geschrieben - auch die ID's zurück, die zu nur einmaligen Zeilen gehören. Also kann diese Unterabfrage vereinfacht werden, indem sofort über die ganze Tabelle gruppiert und pro Datenzeile das Minimum ermittelt wird:

SELECT A.[Personen-Id] As [Datensatz-ist-Kandidat-zum-Loeschen],
	A.Nachname, A.Vorname, A.Geburtstag

-- alternativ: DELETE

FROM tbl_Personen As A

Where A.[Personen-Id] NOT IN

	(SELECT MIN(X.[Personen-Id])
		FROM tbl_Personen As X
		GROUP BY X.Nachname, X.Vorname, X.Geburtstag)

Vergleichen Sie diese Lösung mit den vorherigen Beispielen, so entspricht dies dem zweiten Codebeispiel, es wurde lediglich die HAVING - Klausel entfernt.

Für die tatsächliche Löschabfrage ersetzen Sie die SELECT-Zeile durch DELETE und entfernen As A sowie das spätere Präfix A. Bei DELETE-Befehlen ist kein Präfix erlaubt. Das Ergebnis:

DELETE FROM tbl_Personen

Where [Personen-Id] NOT IN

	(SELECT MIN(X.[Personen-Id])
		FROM tbl_Personen As X
		GROUP BY X.Nachname, X.Vorname, X.Geburtstag)

Das Sql-Script können Sie bei einer vertrauten Verbindung und lokalem Server mit dem folgenden Dos-Befehl ausführen:

OSQL -S (local) -E -i Doppelte-Daten-Zeilen-loeschen.sql -d Ihre_Datenbank

Erstellen Sie zuvor eine Datenbank 'Ihre_Datenbank', die fernab von Ihren Produktions-Datenbanken ist. Das Script zeigt zum Schluß zunächst die Tabelle an und führt dann einmal die gespeicherte Prozedur aus, so daß die zu löschenden Datensätze mit den ID 2, 3 und 5 angezeigt werden.