Delete Duplicate Rows (using CTE)

We can Create a CTE on a table, and add to the table more 'columns', and when CTE is created - DELETE statement can be run on it.

When DELETE command is executed over CTE it in fact deletes from the base table used in CTE.

CREATE TABLE DuplicateRcordTable (Col1 INT, Col2 INT)

GO

INSERT INTO DuplicateRcordTable

VALUES

(1,1),

(1,1), --duplicate

(1,1), --duplicate

(1,2),

(1,2), --duplicate

(1,3),

(1,4)

GO

SELECT * FROM DuplicateRcordTable

GO

WITH CTE (COl1,Col2, DuplicateCount)

AS

(

SELECT COl1,Col2,

ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount

FROM DuplicateRcordTable

)

DELETE

FROM CTE

WHERE DuplicateCount > 1

GO

SELECT * FROM DuplicateRcordTable

GO

DROP TABLE DuplicateRcordTable

GO

-------------------------------------------

-- Another example:

CREATE TABLE DuplicateRcordTable (Col1 INT, Col2 INT, Col3 INT)

GO

INSERT INTO DuplicateRcordTable

VALUES

(1,1,5),

(1,1,4), --duplicate

(1,1,1), --duplicate

(1,2,1),

(1,2,5), --duplicate

(1,3,4),

(1,4,1)

GO

SELECT * FROM DuplicateRcordTable

GO

WITH CTE (COl1,Col2, DuplicateCount)

AS

(

SELECT COl1,Col2,

ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount

FROM DuplicateRcordTable

)

DELETE

FROM CTE

WHERE DuplicateCount > 1

GO

SELECT * FROM DuplicateRcordTable

GO

DROP TABLE DuplicateRcordTable

GO