How to DELETE duplicate records from a table?

Delete Duplicate records

It is possible with CTE(Common Table Expresssion). Let us say, duplicated records are there in a table with the same values for all the columns.

Now we have to delete 2 duplicated rows from the below record set.

Since all the column values are same, we can’t use WHERE condition in this case. This can be possible by using ROW_NUMBER() function.

Delete Duplicate Records

Any of the two records should be deleted out of three records in the above table.

————————————————————————————————————————————-
WITH DeleteDup
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY EmployeeID) AS RowNum
FROM dbo.DuplicatedEmployee
)

— Delete the duplicate records.
DELETE FROM DeleteDup WHERE RowNum > 1
— Now Check the data.
SELECT * FROM dbo.DuplicatedEmployee
————————————————————————————————————————————-

Leave a Reply

Your email address will not be published. Required fields are marked *