Delete duplicate rows from a table in SQL Server

Suppose you get a requirement to delete duplicate rows from SQL Server table but the condition is delete all the duplicate rows but keeps only one occurrence of each duplicate group.

Lets try to understand requirement with the help of some sample data, as you can see in the first image (left side), there are few duplicate rows in table that is row numbers (1,2 & 3), (5 &6) ,(8 ,9 & 10) are duplicates .

So, the goal is to delete all the duplicate rows but keeps only one occurrence of each duplicate group as given in another sample image (right side).

To delete the duplicate records from a table we Use CTE statement with Row_Number() as given below .

WITH CTE_RAWDATA AS (
SELECT
ID,
Name,
code,
ROW_NUMBER() OVER (
PARTITION BY ID, Name, code ORDER BY ID, Name, code
) Seqnum
FROM
RawData
)
DELETE FROM CTE_RAWDATA WHERE SeqNum >1

 

 

Lets verify the table to ensure that duplicate rows are deleted, for that just select records from table and we can see now there are no duplicate in table now.

SELECT * FROM RawData

Also Read

CTE ( common table Expression)

Delete command