usr021986 usr021986 - 2 months ago 9
SQL Question

Delete duplicate records in SQL Server?

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

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;
Comments