How to Delete Duplicate Records in SQL Server Based on a Specific Field Using a Single Query?

Delete duplicate records in SQL Server based on specific fields

Suppose there is a table named "Employee" which contains a column named "EmployeeName". The task is to delete redundant records based on the "EmployeeName" field and obtain the following data:

EmployeeName

Anand

Anil

Dipak

How can I achieve this with a single query using TSQL in SQL Server?

Solution using window functions:

To solve this problem, window function is a feasible method. Window functions allow you to perform operations on a set of rows within a partition defined by the OVER clause. In this example, the partition is defined by the "EmployeeName" column.

The following query effectively identifies and removes duplicate records based on the "EmployeeName" field:

<code class="language-sql">delete x from (
select *, rn=row_number() over (partition by EmployeeName order by empId)
from Employee
) x
where rn > 1;</code>

Explanation:

• The window function row_number() is used to assign a numeric rank - starting from 1 - to each record in each partition (defined by EmployeeName).
• The delete statement then targets rows with rn greater than 1, which are duplicates.

View deleted records:

To preview the records that will be deleted without actually executing the delete statement, use the following select query:

<code class="language-sql">select *
from (
select *, rn=row_number() over (partition by EmployeeName order by empId)
from Employee
) x
where rn > 1;</code>

The above is the detailed content of How to Delete Duplicate Records in SQL Server Based on a Specific Field Using a Single Query?. For more information, please follow other related articles on the PHP Chinese website!