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 or not, for that just select records from table and we can see now there are no duplicate in table now.
SELECT * FROM RawData
CTE ( common table Expression)
1,464 total views, 1 views today