Wie lösche ich doppelte Zeilen in SQL Server?

Question

Wie kann ich doppelte Zeilen löschen, wenn keine einzigartige Zeilen-ID vorhanden ist?

Meine Tabelle ist

col1  col2 col3 col4 col5 col6 col7
john  1    1    1    1    1    1 
john  1    1    1    1    1    1
sally 2    2    2    2    2    2
sally 2    2    2    2    2    2

Nach dem Entfernen der Duplikate möchte ich folgendes übrig haben:

john  1    1    1    1    1    1
sally 2    2    2    2    2    2

Ich habe schon einige Abfragen ausprobiert, aber ich glaube, sie hängen davon ab, eine Zeilen-ID zu haben, da ich nicht das gewünschte Ergebnis erhalte. Zum Beispiel:

DELETE
FROM table
WHERE col1 IN (
    SELECT id
    FROM table
    GROUP BY id
    HAVING (COUNT(col1) > 1)
)

Answer 1

Ich mag CTEs und ROW_NUMBER zusammen, da sie es uns ermöglichen zu sehen, welche Zeilen gelöscht (oder aktualisiert) wurden. Ändern Sie daher einfach das DELETE FROM CTE... zu SELECT * FROM CTE:

WITH CTE AS(
   SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7],
       RN = ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col1)
   FROM dbo.Table1
)
DELETE FROM CTE WHERE RN > 1

<a href="http://rextester.com/ZHFH51082" rel="noreferrer"><strong>DEMO</strong></a> (Ergebnis ist unterschiedlich; ich nehme an, dass dies auf einen Tippfehler Ihrerseits zurückzuführen ist)

COL1    COL2    COL3    COL4    COL5    COL6    COL7
john    1        1       1       1       1       1
sally   2        2       2       2       2       2

In diesem Beispiel werden Duplikate anhand einer einzelnen Spalte col1 ermittelt, aufgrund des PARTITION BY col1. Wenn Sie mehrere Spalten hinzufügen möchten, fügen Sie sie einfach dem PARTITION BY hinzu:

ROW_NUMBER()OVER(PARTITION BY Col1, Col2, ... ORDER BY OrderColumn)

Answer 2

Ich würde CTE bevorzugen, um doppelte Zeilen aus der SQL Server-Tabelle zu löschen

empfehlen dringend, diesem Artikel zu folgen ::http://codaffection.com/sql-server-article/delete-duplicate-rows-in-sql-server/

indem das Original beibehalten wird

MIT CTE ALS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY col1,col2,col3 ORDER BY col1,col2,col3) AS RN
FROM MyTable
)

DELETE FROM CTE WHERE RN<>1

ohne das Original beizubehalten

MIT CTE ALS
(SELECT *,R=RANK() OVER (ORDER BY col1,col2,col3)
FROM MyTable)
 
DELETE CTE
WHERE R IN (SELECT R FROM CTE GROUP BY R HAVING COUNT(*)>1)

Answer 3

Ohne die Verwendung von CTE und ROW_NUMBER() können Sie die Datensätze einfach löschen, indem Sie GROUP BY mit der MAX-Funktion verwenden. Hier ist ein Beispiel:

DELETE
FROM MyDuplicateTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyDuplicateTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

Answer 4

Wenn Sie keine Referenzen haben, wie z. B. Fremdschlüssel, können Sie dies tun. Ich mache das oft, wenn ich Konzeptnachweise teste und die Testdaten dupliziert werden.

SELECT DISTINCT [col1],[col2],[col3],[col4],[col5],[col6],[col7]

INTO [newTable]

FROM [oldTable]

Gehen Sie in den Objekt-Explorer und löschen Sie die alte Tabelle.

Benennen Sie die neue Tabelle mit dem Namen der alten Tabelle um.

Answer 5

Entfernen Sie alle Duplikate, aber nur die ersten (mit minimaler ID)

Sollte auch in anderen SQL-Servern wie Postgres funktionieren:

DELETE FROM tabelle
WHERE id NOT IN (
   select min(id) from tabelle
   group by spalte1, spalte2, spalte3, spalte4, spalte5, spalte6, spalte7
)