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.
Any of the two records should be deleted out of three records in the above table.
SELECT ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY EmployeeID) AS RowNum
— Delete the duplicate records.
DELETE FROM DeleteDup WHERE RowNum > 1
— Now Check the data.
SELECT * FROM dbo.DuplicatedEmployee