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 DupsNumbered
AS (
SELECT Col1, Col2, Row_Number()
OVER (PARTITION BY Col1, Col2 ORDER BY Col1) AS rn
FROM DupsNoPK
)
DELETE DupsNumbered
WHERE rn > 2;
2. Surrogate key(richiede ALTER TABLE aggiungendo un IDENTITY)
DELETE DupsNoPK
WHERE 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