How to delete duplicate from a table ?

Delete duplicates from table in SQL

There are different ways to delete duplicates from a table, we often get duplicates while loading data in tables which does not contain any primary key.

Lets say I have below table and we need to delete duplicates from the table.

EmpName

Ram

Gopi

Ram

Radha

Delete duplicates with help of temp table

We first try to load the distinct records in a temporary table and then delete all records from main table and the reload the main table from temporary table.

SELECT DISTINCT EmpName INTO #Data FROM Employee

DELETE FROM Employee

INSERT INTO Employee (EmpName)

SELECT EmpName FROM #Data

Delete duplicate using CTE

We use row_number() function and common table expression to delete duplicate.

;with cte as (

SELECT *,ROW_NUMBER() OVER (PARTITION BY EmpName ORDER BY EmpName) AS RNK

FROM Employee

)

DELETE FROM cte WHERE RNK > 1