delete duplicate records in SQL Server

Question

Consider a column named EmployeeName table Employee. The goal is to delete repeated records, based on the EmployeeName field.

EmployeeName
------------
Anand
Anand
Anil
Dipak
Anil
Dipak
Dipak
Anil

Using one query, I want to delete the records which are repeated.

How can this be done with TSQL in SQL Server?

Answer 1

You could try something like the following:

delete T1
from MyTable T1, MyTable T2
where T1.dupField = T2.dupField
and T1.uniqueField > T2.uniqueField  

(this assumes that you have an integer based unique field)

Personally though I'd say you were better off trying to correct the fact that duplicate entries are being added to the database before it occurs rather than as a post fix-it operation.

Answer 2

Assuming that your Employee table also has a unique column (ID in the example below), the following should work:

delete from Employee 
where id NOT in
(
select Min(ID)
from Employee 
group by EmployeeName 
)

it will leave the version with the lowest ID in the table

Answer 3

You can do this with window functions. It will order the dupes by empId, and delete all but the first one.

delete x from (
  select *, rn=row_number() over (partition by EmployeeName order by empId)
  from Employee 
) x
where rn > 1;

Run it as a select to see what would be deleted:

select *
from (
  select *, rn=row_number() over (partition by EmployeeName order by empId)
  from Employee 
) x
where rn > 1;

Answer 4

WITH CTE AS ( SELECT EmployeeName,ROW_NUMBER OVER(PARTITION BY EmployeeName ORDER BY EmployeeName) AS R ) DELETE CTE WHERE R > 1;

The magic of common table expressions.