How to Delete Duplicate Records in SQL Server Using a Single T-SQL Query?

Efficiently Removing Duplicate Rows in SQL Server

Imagine an Employee table containing an EmployeeName column. The goal is to remove duplicate rows based solely on the EmployeeName field. This can be achieved using a concise T-SQL query in SQL Server.

The Solution:

Leveraging window functions provides an elegant solution. The following query assigns a unique row number (rn) to each record within each EmployeeName group, ordered by empId. Subsequently, rows where rn is greater than 1 (i.e., duplicates) are deleted.

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

To preview the rows slated for deletion before executing the DELETE statement, run this 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 Using a Single T-SQL Query?. For more information, please follow other related articles on the PHP Chinese website!