Wie kann ich doppelte Zeilen entfernen?

Question

Wie entfernt man am besten doppelte Zeilen aus einer ziemlich großen SQL Server Tabelle (d.h. 300.000+ Zeilen)?

Die Zeilen werden natürlich keine perfekten Duplikate sein, da es die RowID Identitätsfeld.

MyTable

RowID int not null identity(1,1) primary key,
Col1 varchar(20) not null,
Col2 varchar(2048) not null,
Col3 tinyint not null

Answer 1

Ich bevorzuge die Unterabfrage \having count(*) > 1 Lösung für die innere Verknüpfung, weil ich sie leichter zu lesen fand und sie sehr einfach in eine SELECT-Anweisung umzuwandeln war, um zu überprüfen, was gelöscht werden würde, bevor Sie sie ausführen.

--DELETE FROM table1 
--WHERE id IN ( 
     SELECT MIN(id) FROM table1 
     GROUP BY col1, col2, col3 
     -- could add a WHERE clause here to further filter
     HAVING count(*) > 1
--)

Answer 2

SELECT  DISTINCT *
      INTO tempdb.dbo.tmpTable
FROM myTable

TRUNCATE TABLE myTable
INSERT INTO myTable SELECT * FROM tempdb.dbo.tmpTable
DROP TABLE tempdb.dbo.tmpTable

Answer 3

Ich dachte, ich teile meine Lösung mit, da sie unter besonderen Umständen funktioniert. In meinem Fall hatte die Tabelle mit den doppelten Werten keinen Fremdschlüssel (weil die Werte aus einer anderen Datenbank dupliziert wurden).

begin transaction
-- create temp table with identical structure as source table
Select * Into #temp From tableName Where 1 = 2

-- insert distinct values into temp
insert into #temp 
select distinct * 
from  tableName

-- delete from source
delete from tableName 

-- insert into source from temp
insert into tableName 
select * 
from #temp

rollback transaction
-- if this works, change rollback to commit and execute again to keep you changes!!

PS: Wenn ich an solchen Dingen arbeite, verwende ich immer eine Transaktion, die nicht nur sicherstellt, dass alles als Ganzes ausgeführt wird, sondern mir auch erlaubt, zu testen, ohne etwas zu riskieren. Aber natürlich sollte man trotzdem ein Backup machen, nur um sicher zu gehen...

Answer 4

CTE verwenden. Die Idee besteht darin, eine oder mehrere Spalten zu verbinden, die einen doppelten Datensatz bilden, und dann zu entfernen, was immer Sie wollen:

;with cte as (
    select 
        min(PrimaryKey) as PrimaryKey
        UniqueColumn1,
        UniqueColumn2
    from dbo.DuplicatesTable 
    group by
        UniqueColumn1, UniqueColumn1
    having count(*) > 1
)
delete d
from dbo.DuplicatesTable d 
inner join cte on 
    d.PrimaryKey > cte.PrimaryKey and
    d.UniqueColumn1 = cte.UniqueColumn1 and 
    d.UniqueColumn2 = cte.UniqueColumn2;

Answer 5

Diese Abfrage ergab für mich eine sehr gute Leistung:

DELETE tbl
FROM
    MyTable tbl
WHERE
    EXISTS (
        SELECT
            *
        FROM
            MyTable tbl2
        WHERE
            tbl2.SameValue = tbl.SameValue
        AND tbl.IdUniqueValue < tbl2.IdUniqueValue
    )

er löschte 1M Zeilen in etwas mehr als 30sec aus einer Tabelle mit 2M (50% Duplikate)