How to delete duplicate Rows from a table in SQL Server ?

Sometime we have a requirement to delete duplicate rows from SQL Server table like to deletes 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 we can see in the first image (left side) , there are duplicate rows in a table like row numbers (1,2 & 3)  , (5 &6) ,(8 ,9 & 10) are duplicates .

So our 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 or not , we select records from table and we can see now there are no duplicate in table now.

SELECT * FROM RawData

 

Recommended Posts ..

CTE ( common table Expression)

Delete command

 102 total views,  1 views today