Entfernen doppelter Zeilen aus einer SQL Server-Tabelle mithilfe eines Skripts

Ursprüngliche Produktversion: SQL Server
Ursprüngliche KB-Nummer: 70956

Zusammenfassung

In diesem Artikel wird gezeigt, wie Doppelte Zeilen aus einer Tabelle in Microsoft SQL Server mithilfe eines Transact-SQL (T-SQL)-Skripts entfernt werden. Es beschreibt zwei gängige Methoden: Kopieren einer Instanz jeder doppelten Zeile in eine separate Tabelle und Verwenden der ROW_NUMBER Funktion zum Löschen der zusätzlichen Zeilen direkt in der Tabelle. Verwenden Sie diesen Hilfeartikel, wenn eine Tabelle wiederholte Datensätze enthält, z. B. weil sie keine eindeutige Einschränkung oder keinen Primärschlüssel aufweist. Methode 2 ist einfacher, da sie direkt arbeitet.

Erstellen Sie eine Beispieltabelle mit doppelten Zeilen

Erstellen Sie zum Veranschaulichen beider Methoden eine Beispieltabelle namens original_table, die eine Spalte, key_value, hat, und fügen Sie doppelte Daten hinzu:

CREATE TABLE original_table (key_value int )

INSERT INTO original_table values (1)
INSERT INTO original_table values (1)
INSERT INTO original_table values (1)

INSERT INTO original_table values (2)
INSERT INTO original_table values (2)
INSERT INTO original_table values (2)
INSERT INTO original_table values (2)

Verwenden Sie dann eine der folgenden Methoden, um die doppelten Zeilen aus der Tabelle zu entfernen.

Methode 1: Doppelte Zeilen in eine separate Tabelle verschieben und eine einzelne Kopie wieder einfügen

Methode 1 verwendet zwei Tabellen: original_table und eine zweite Tabelle, duplicate_table, die das Skript erstellt und dann löscht. Wenn Sie das Skript auf eine andere Tabelle anwenden möchten, ersetzen Sie original_table durch den Tabellennamen. Für diese Methode ist ein Umschreiben der GROUP BY und COUNT Logik erforderlich, wenn ein Duplikat durch mehrere Spalten definiert wird.

Führen Sie folgendes Skript aus:

SELECT DISTINCT *
INTO duplicate_table
FROM original_table
GROUP BY key_value
HAVING COUNT(key_value) > 1

DELETE original_table
WHERE key_value
IN (SELECT key_value
FROM duplicate_table)

INSERT original_table
SELECT *
FROM duplicate_table

DROP TABLE duplicate_table

Dieses Skript führt die folgenden Aktionen in der angegebenen Reihenfolge aus:

• Verschiebt eine Instanz einer beliebigen doppelten Zeile in der ursprünglichen Tabelle in eine Duplikattabelle.
• Löscht alle Zeilen aus der ursprünglichen Tabelle, die sich auch in der Duplikattabelle befinden.
• Verschiebt die Zeilen in der duplizierten Tabelle wieder in die ursprüngliche Tabelle.
• Löscht die duplizierte Tabelle.

Diese Methode ist einfach. Es erfordert jedoch, dass in der Datenbank genügend Speicherplatz zur Verfügung steht, um vorübergehend die duplizierte Tabelle zu erstellen. Diese Methode verursacht auch Mehraufwand, da Sie die Daten verschieben.

Wenn ihre Tabelle über eine IDENTITÄTsspalte verfügt, müssen Sie außerdem SET IDENTITY_INSERT ON verwenden, wenn Sie die Daten in der ursprünglichen Tabelle wiederherstellen.

Methode 2: Verwenden der ROW_NUMBER-Funktion zum Löschen doppelter Zeilen

Die ROW_NUMBER Funktion, die in Microsoft SQL Server 2005 eingeführt wurde, macht diesen Vorgang wesentlich einfacher. Ersetzen Sie key_value durch die Spalte oder Spalten, die ein Duplikat definieren.

DELETE T
FROM
(
SELECT *
, DupRank = ROW_NUMBER() OVER (
              PARTITION BY key_value
              ORDER BY (SELECT NULL)
            )
FROM original_table
) AS T
WHERE DupRank > 1 

Dieses Skript führt die folgenden Aktionen in der angegebenen Reihenfolge aus:

• Verwendet die ROW_NUMBER Funktion, um die Daten auf der Grundlage zu partitionieren, wobei key_value aus einer oder mehreren, durch Kommas getrennten Spalten bestehen kann.
• Löscht alle Datensätze, die einen DupRank Wert erhalten haben, der größer als 1 ist. Dieser Wert zeigt an, dass die Datensätze Duplikate sind.

Aufgrund des (SELECT NULL) Ausdrucks sortiert das Skript die partitionierten Daten nicht basierend auf einer Bedingung. Wenn Ihre Logik zum Löschen von Duplikaten erfordert, auszuwählen, welche Datensätze gelöscht und welche basierend auf der Sortierreihenfolge anderer Spalten beibehalten werden sollen, können Sie dazu den ORDER BY Ausdruck verwenden.

Behalten Sie die neueste Zeile bei, wenn Sie Duplikate entfernen

Um die Zeile mit dem neuesten Wert beizubehalten, sortieren Sie in absteigender Reihenfolge nach dieser Spalte. Betrachten Sie z. B. eine Hypothetische employee_table , die mehrere Zeilen für denselben Mitarbeiter enthält. Das folgende Skript behält nur die Zeile mit dem neuesten updated_date Wert bei:

DELETE T
FROM
(
SELECT *
, DupRank = ROW_NUMBER() OVER (
              PARTITION BY employee_name
              ORDER BY updated_date DESC
            )
FROM employee_table
) AS T
WHERE DupRank > 1

Die Zeile mit dem aktuellsten updated_date Wert erhält einen DupRank Wert von 1, sodass das Skript sie behält und die älteren Zeilen löscht.

Warum wird Methode 2 empfohlen?

Methode 2 ist aus diesen Gründen einfach und effektiv:

• Es ist nicht erforderlich, dass Sie die doppelten Datensätze vorübergehend in eine andere Tabelle kopieren.
• Es ist nicht erforderlich, dass Sie die ursprüngliche Tabelle mit sich selbst verbinden (z. B. mithilfe einer Unterabfrage, die mithilfe einer Kombination von GROUP BY und HAVING alle doppelten Datensätze zurückgibt).
• Um eine optimale Leistung zu erzielen, sollten Sie über einen entsprechenden Index für die Tabelle verfügen, die key_value als Indexschlüssel verwendet und alle Sortierspalten enthält, die Sie möglicherweise im ORDER BY-Ausdruck verwendet haben.

Die ROW_NUMBER Funktion ist in SQL Server 2005 und höheren Versionen verfügbar. Wenn Sie eine Version von SQL Server verwenden, die die ROW_NUMBER-Funktion nicht unterstützt, verwenden Sie stattdessen Methode 1 oder eine ähnliche Methode.

Wie kann ich doppelte Zeilen in einer Tabelle verhindern?

Nachdem Sie die Duplikate entfernt haben, können Sie neue doppelte Zeilen verhindern, indem Sie eine Unique-Constraint oder einen eindeutigen Index für die Spalte oder Spalten definieren, die eine Zeile identifizieren.

Verwandte Inhalte

• DELETE (Transact-SQL)
• OVER-Klausel (Transact-SQL)
• MIT common_table_expression (Transact-SQL)