Delete Duplicate Record from Table in SQL Server

Below query can help us to delete the duplication record from table using CTE.

Below query will delete the duplicate record from physical table using CTE table.

 

WITH CTE (EmpId, EmpName, ADDRESS, Designation, Age, DuplicateCount)

AS

(

SELECT EmpId, EmpName, ADDRESS, Designation, Age,

ROW_NUMBER() OVER(PARTITION BY EmpId, EmpName, ADDRESS, Designation, Age ORDER BY EmpID) AS DuplicateCount

FROM dbo.Emp

)

DELETE FROM CTE WHERE DuplicateCount > 1