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?
Advertisement
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;