Entfernen von doppelten Zeilen aus einer Tabelle in SQL Server

Artikel-ID: 139444 - Produkte anzeigen, auf die sich dieser Artikel bezieht
Dieser Artikel ist eine Übersetzung des folgenden englischsprachigen Artikels der Microsoft Knowledge Base:
139444
(http://support.microsoft.com/kb/139444/EN-US/ )
How to remove duplicate rows from a table in SQL Server
Bitte beachten Sie: Bei diesem Artikel handelt es sich um eine Übersetzung aus dem Englischen. Es ist möglich, dass nachträgliche Änderungen bzw. Ergänzungen im englischen Originalartikel in dieser Übersetzung nicht berücksichtigt sind. Die in diesem Artikel enthaltenen Informationen basieren auf der/den englischsprachigen Produktversion(en). Die Richtigkeit dieser Informationen in Zusammenhang mit anderssprachigen Produktversionen wurde im Rahmen dieser Übersetzung nicht getestet. Microsoft stellt diese Informationen ohne Gewähr für Richtigkeit bzw. Funktionalität zur Verfügung und übernimmt auch keine Gewährleistung bezüglich der Vollständigkeit oder Richtigkeit der Übersetzung.
Alles erweitern | Alles schließen

Zusammenfassung

Microsoft SQL Server-Tabellen sollten niemals doppelte Zeilen oder nicht-eindeutige Primärschlüssel enthalten. Primärschlüssel werden in diesem Artikel manchmal als "Schlüssel" oder "PK" bezeichnet, dies bedeutet aber immer "Primärschlüssel". 2 doppelte PS´s stellen eine Verletzung der Einheitenintegrität dar, und sollten daher in einem relationalem System nicht erlaubt sein. SQL Server verfügt über mehrere Mechanismen, um die Einheitenintegrität durchzusetzen. Unter anderem Indizes, UNIQUE-Einschränkungen, PRIMARY KEY-Einschränkungen (Primärschlüssel) und Trigger.

Trotzdem kann es unter ungewöhnlichen Umständen vorkommen, dass doppelte Primärschlüssel auftreten. Ist dies der Fall, müssen sie entfernt werden. Eine Möglichkeit warum dies auftreten kann ist, wenn doppelte PKs in nicht-relationalen Daten außerhalb von SQL Server existieren, und die Daten importiert werden, ohne dass die PK-Einzigartigkeit durchgesetzt wird. Eine weitere Möglichkeit ist ein Fehler im Datenbankdesign, wie beispielsweise dass die Einheitenintegrität nicht in jeder Tabelle durchgesetzt wird.

Oft werden doppelte PKs beim Versuch erkannt, einen eindeutigen Index zu erstellen, wobei es zum Abbruch kommt, wenn doppelte Schlüssel gefunden werden. Die Meldung lautet (sinngemäß):
Meldung 1505, Schweregrad 16, Status 1 Erstellung des eindeutigen Index bei doppeltem Schlüssel abgebrochen.
Wenn Sie SQL Server 2000 oder SQL Server 2005 verwenden, wird möglicherweise die folgende Fehlermeldung angezeigt (sinngemäß):
Meldung 1505, Schweregrad 16, Status 1 CREATE UNIQUE INDEX beendet, da für Objektname "%.*ls" und Indexname "%.*ls" ein doppelter Schlüssel gefunden wurde. Der doppelte Schlüsselwert ist %ls.
Dieser Artikel beschreibt, wie doppelte Primärschlüssel aus einer Tabelle gefunden und entfernt werden können. Trotzdem sollten Sie den Prozess, der zur Duplikation geführt hat, genau untersuchen, um ein erneutes Auftreten zu verhindern.

Weitere Informationen

In diesem Beispiel wird die folgende Tabelle mit doppelten PK-Werten verwendet. In dieser Tabelle besteht der Primärschlüssel aus den beiden Spalten (col1, col2). Wir können keinen eindeutigen Index oder PRIMARY KEY-Einschränkung erstellen, da sich in zwei Zeilen doppelte PKs befinden. Diese Prozedur veranschaulicht, wie Sie die Duplikate finden und entfernen. 
create table t1(col1 int, col2 int, col3 char(50))
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 2, 'data value two')
Im ersten Schritt muss festgestellt werden, in welchen Zeilen sich doppelte Primärschlüsselwerte befinden: 
SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1
Hierdurch wird eine Zeile für jeden Satz doppelter PS-Werte in der Tabelle zurückgegeben. Die letzte Spalte in diesem Ergebnis ist die Nummer der Duplikate für den entsprechenden PS-Wert.

Tabelle minimierenTabelle vergrößern
col1col2
112


Wenn es sich nur um wenige Sätze doppelter PS-Werte handelt, ist es am besten, diese einzeln manuell zu löschen. Beispiel: 
set rowcount 1
delete from t1
where col1=1 and col2=1
Der Wert "Rowcount" sollte n-1 die Zahl der Duplikate für einen bestimmten Wert sein. In diesem Beispiel gibt es 2 Duplikate, daher ist der "Rowcount" auf 1 gesetzt. Die Werte col1/col2 werden aus den obenstehenden Abfrageergebnissen "GROUP BY" (Gruppieren nach) bezogen. Wenn die Abfrage "GROUP BY" mehrere Zeilen zurückgibt, muss die Abfrage "set rowcount" einmal für jede dieser Zeilen ausgeführt werden. Setzen Sie bei jeder Ausführung "Rowcount" auf n-1 der Zahl der Duplikate des bestimmten PK-Wertes.

Bevor Sie die Zeilen löschen, sollten Sie sich vergewissern, dass die gesamte Zeile doppelt vorhanden ist. Auch wenn es unwahrscheinlich ist, ist es möglich, dass die PK-Werte doppelt vorhanden sind, die Zeile als Ganzes aber nicht. Ein Beispiel hierfür wäre eine Tabelle mit einer Sozialversicherungsnummer als Primärschlüssel aber zwei verschiedene Menschen (oder Zeilen) und jeder mit eindeutigen Attributen. In diesem Fall, kann die Fehlfunktion, die zu dem doppelten Schlüssel geführt hat, ebenso dazu geführt haben, dass gültige, eindeutige Daten in die Zeile geschrieben wurden. Diese Daten sollten vor dem Löschen herauskopiert und aufbewahrt werden, um diese zu studieren und eventuell einen Abgleich auszuführen.

Wenn zu viele unterschiedliche Sätze von doppelten PK-Werten in der Tabelle vorhanden sind, kann das manuelle Entfernen dieser zu zeitaufwändig sein. In diesem Fall kann die folgende Prozedur angewendet werden:
  1. Führen Sie als erstes die obenstehende Abfrage "GROUP BY" aus, um festzustellen, welche PK-Werte bereits existieren und wie viele Duplikate sich in jedem Satz befinden.
  2. Wählen Sie die doppelten Schlüsselwerte aus, und kopieren Sie diese in eine temporäre Tabelle. Beispiel:
    SELECT col1, col2, col3=count(*)
INTO holdkey
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1
  3. Wählen Sie die doppelten Schlüsselwerte aus, kopieren Sie diese in eine temporäre Tabelle. Dabei entfernen Sie die Duplikate. Beispiel:
    SELECT DISTINCT t1.*
INTO holddups
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2
  4. An diesem Punkt sollten sich in der temporären Tabelle nur eindeutige PK´s befinden. Dies wäre aber nicht der Fall, wenn sich in t1 doppelte PK´s befinden würden, aber eindeutige Zeilen (wie in obenstehendem SSN-Beispiel). Vergewissern Sie sich, dass jeder Schlüssel in der temporären Tabelle eindeutig ist und keine doppelten Schlüssel bei eindeutigen Zeilen vorhanden sind. Wenn dies der Fall sein sollte, müssen Sie hier stoppen und abgleichen, welche der Zeilen Sie für einen doppelten Schlüsselwert beibehalten möchten. Beispielsweise sollte die Abfrage 
    SELECT col1, col2, count(*)
FROM holddups
GROUP BY col1, col2
    einen Wert von 1 für jede Zeile ergeben. Wenn dies zutrifft, gehen Sie zu untenstehendem Schritt 5. Wenn dies nicht zutrifft, gibt es doppelte Schlüssel bei eindeutigen Zeilen, und Sie müssen sich entscheiden, welche Zeilen Sie beibehalten möchten. Normalerweise erfordert dies, eine Zeile zu verwerfen, oder einen neuen eindeutigen Schlüsselwert für diese Zeile zu erstellen. Führen Sie beide dieser Schritte für jede doppelte PS in der temporären Tabelle durch.
  5. Löschen Sie die doppelten Zeilen aus der Originaltabelle. Beispiel:
    DELETE t1
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2
  6. Setzen Sie die eindeutigen Reihen zurück in die Originaltabelle. Beispiel:
    INSERT t1 SELECT * FROM holddups

Eigenschaften

Artikel-ID: 139444 - Geändert am: Freitag, 18. Februar 2011 - Version: 5.0
Die Informationen in diesem Artikel beziehen sich auf:
  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Keywords: 
kbsqlssrs kbinfo kbusage KB139444
Microsoft stellt Ihnen die in der Knowledge Base angebotenen Artikel und Informationen als Service-Leistung zur Verfügung. Microsoft übernimmt keinerlei Gewährleistung dafür, dass die angebotenen Artikel und Informationen auch in Ihrer Einsatzumgebung die erwünschten Ergebnisse erzielen. Die Entscheidung darüber, ob und in welcher Form Sie die angebotenen Artikel und Informationen nutzen, liegt daher allein bei Ihnen. Mit Ausnahme der gesetzlichen Haftung für Vorsatz ist jede Haftung von Microsoft im Zusammenhang mit Ihrer Nutzung dieser Artikel oder Informationen ausgeschlossen.
Zum Anfang | Ihr Feedback an uns

Ihr Feedback an uns

 
Zum AnfangZum Anfang