How to Delete Duplicate Records in SQL Server Using TSQL?

Removing Duplicate Entries in SQL Server Tables

Problem:

Suppose you have an Employee table with a field called EmployeeName containing duplicate entries. How can you use T-SQL to remove these duplicate rows based on the EmployeeName column in SQL Server?

Solution:

Efficiently delete duplicate rows in SQL Server using T-SQL and window functions. By ordering the duplicates using an employee ID, we can retain only the first occurrence of each name.

<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 review the rows slated for deletion before executing the DELETE statement, run this 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 TSQL?. For more information, please follow other related articles on the PHP Chinese website!