Doppelte Datensätze bereinigen

[PranKe01]

Hi,

ich habe eine Tabelle in der Benutzerdaten enthalten sind. Leider gibt es dort auch Duplikate, welche ich bereinigen will. Die Tabelle enthält unter anderem folgende Spalten:

Id
ExternId
Name
Title
Street

Jeder Eintrag hat eine unterschiedliche Id. Allerdings handelt es sich nur um ein Duplikat, wenn die ExternId gleich ist - es können aber auch zwei verschiedene Personen die selbe ExternId besitzen. Allerdings ist dann sichergestellt, dass diese Personen nicht den gleichen Namen haben. Drei Beispieldatensätze könnten so aussehen:

1 3 Jens Herr NULL
2 3 Jens NULL Marktplatz
3 7 Hans Herr NULL

Rauskommen soll am Ende folgendes:

1 3 Jens Herr Marktplatz
3 7 Hans Herr NULL

Folgendes habe ich bisher:

SELECT	d1.Id,
	d1.ExternId,
	d1.Name,
	ISNULL(d1.Title, d2.Title), 
	ISNULL(d1.Street, d2.Street)
FROM Users d1, Users d2
WHERE d1.Name = d2.Name AND d1.ExternId = d2.ExternId AND d1.Id <> d2.Id

Das Problem, das ich jetzt habe ist, wie ich jetzt die Datensätze zusammenfasse und die doppelten entferne!? Oder gibt es vlt. eine einfachere Methode sowas durchzuführen? Ggf. ein Programm oder eine Funktion?

Danke für Eure Hilfe

[Falk Prüfer]

Hallo,

"doppelte" Datensätze findet man i.a.R. mit einer Gruppierung und HAVING COUNT.
z.B:
[highlight=sql]
select ExternId, Name
from Users
group by ExternId, Name
having count(*) > 1
[/highlight]

Um jetzt auf dieser Basis mittels Subquery die Dupletten löschen zu können, benötigts du noch ein eindeutiges Kriterium für den DS (der doppelten) der NICHT gelöscht werden soll. Das könnte z.B. immer der mit der kleinsten ID sein.
Anhand deines Bsp. vermute ich aber mal, dass du eher auch gleich Daten (Anrede, Strasse, etc) aus mehreren DS zusammenfassen und NULL-Werte ersetzen willst. Dafür wirst du nicht umhin kommen, zuerst die Zusammenfassung in eine temporäre Tabelle zu schreiben, dann ALLE Dupletten zu löschen und anschließend die DS aus der Temp-Tabelle zurückzukopieren.
Einfacher geht das meines Erachtens nicht und eine vorgefertigte Funktion gibts dafür auch nicht. Auf alle Fälle solltest du nach der Bereinigung einen passenden UNIQUE-Constraint anlegen, um Dopplungen in Zukunft zu vermeiden.

Gruß Falk

[knoxyz]

Hallo PranKe01,

wie sagt man so schön, die Lösung ist immer einfach wenn man Sie kennt:

SELECT 
	 MIN(ID) ID
	,ExternId
	,Name
	,MIN(Titel) Titel
	,MIN(Street) Street
  FROM Users
  GROUP BY ExternId, Name
  ORDER BY ID

[PranKe01]

Erst einmal danke euch beiden Das mit der temporären Tabelle hatte ich mir auch schon überlegt, fand es aber irgendwie nicht optimal Aber damit kann ich leben.
Noch eine Frage zu dem Code-Beispiel von knoxyz: Du benutzt die MIN Funktion um Titel und Straße zu wählen. Wie könnte man das denn lösen, dass immer die Daten aus dem Datensatz mit der größeren Id gewählt werden und nur die Felder, die NULL sind mit vorherigen Werten gefüllt werden? Hintergrund ist der, dass folgende Datensätze vorhanden sein können:

1 3 Hans Herr Marktplatz
2 3 Hans NULL Bahnhofsplatz

Jetzt könnte es ja passieren, dass folgender Datensatz daraus resultiert:

1 3 Hans Herr Marktplatz

Es sollte aber folgender herauskommen:

1 3 Hans Herr Bahnhofsplatz

Sprich, ich gehe davon aus, dass der Datensatz mit der größeren ID aktueller ist. Allerdings kann bei dem aktuellen etwas fehlen (wie z.B. die Anrede).

Ich hoffe es ist klar, was ich meine

EDIT: Hier mal ein Reales Beispiel, bei dem das falsche Ergebnis heraus kommt (PLZ != Ort):

284124 5669 NULL Michi Steer 67059 Ludwigshafen Bruchwiesenstr. 26 DEU
284126 5669 NULL Michi Steer 67122 Altrip Am Horren 18 DEU

Ergebnis:

284124 5669 NULL Michi Steer 67059 Altrip Am Horren 18 DEU

[knoxyz]

Dann könnte es wie folgt gelöst werden:

SELECT
	 a.ID
	,a.ExternId
	,a.Name
	,Titel	= ISNULL(a.Titel,	(SELECT TOP 1 b.Titel	FROM Users b WHERE a.ID > b.ID AND a.ExternId = b.ExternId AND  a.Name = b.Name ORDER BY b.ID desc))
	,Street = ISNULL(a.Street,	(SELECT TOP 1 b.Street	FROM Users b WHERE a.ID > b.ID AND a.ExternId = b.ExternId AND  a.Name = b.Name ORDER BY b.ID desc))
FROM Users a
WHERE a.ID IN	(SELECT MAX(ID) MinID
		FROM Users
		GROUP BY ExternID, Name
		)

[PranKe01]

Das funktioniert soweit ganz gut. Danke dafür Allerdings werde ich es erweitern müssen, da es ja auch sein kann, dass die zwei neuesten Einträge keine Daten enthalten, der dritte dann aber doch. Im Moment werden ja nur die letzten beiden Einträge angeschaut, nicht aber die darauf folgenden. Mal schauen, ob man da eine Funktion draus erstellen kann Danke!

[knoxyz]

Dafür muss lediglich die Unterabfrage erweitert werden.

SELECT
	 a.ID
	,a.ExternId
	,a.Name
	,Titel	= ISNULL(a.Titel,	(SELECT TOP 1 b.Titel	FROM Users b WHERE a.ID > b.ID AND a.ExternId = b.ExternId AND  a.Name = b.Name AND b.Titel  IS NOT NULL ORDER BY b.ID desc))
	,Street = ISNULL(a.Street,	(SELECT TOP 1 b.Street	FROM Users b WHERE a.ID > b.ID AND a.ExternId = b.ExternId AND  a.Name = b.Name AND b.Street IS NOT NULL ORDER BY b.ID desc))
FROM Users a
WHERE a.ID IN	(SELECT MAX(ID) MinID
		FROM Users
		GROUP BY ExternID, Name
		)

[PranKe01]

Du hast recht, danke