Esistono 3 modi di cancellare righe duplicate da tabelle prive di Primary Key in Sql Server.
Premesso che è molto discutibile avere una tabella priva di Primary Key...
1. Windowing
WITH DupsNumberedAS (SELECT Col1, Col2, Row_Number() OVER (PARTITION BY Col1, Col2 ORDER BY Col1) AS rn FROM DupsNoPK)DELETE DupsNumberedWHERE rn > 2;
2. Surrogate key(richiede ALTER TABLE aggiungendo un IDENTITY)
DELETE DupsNoPKWHERE EXISTS ( SELECT * FROM DupsNoPKAS D1 WHERE D1.Col1 = DupsNoPK.Col1 AND D1.Col2 = DupsNoPK.Col2 AND D1.PK > DupsNoPK.PK);
3. Distinct into
SELECT distinct Col1, Col2 INT NoDups FROM DupsNoPK;Select Cols1, Col2 FROM NoDups;
Technorati tags: Sql Server