How to Efficiently Delete Duplicate Rows in T-SQL Based on Key Columns?

Efficiently delete duplicate rows in T-SQL based on primary key columns

Question:

Due to an error during data creation, the table contains a large number of duplicate rows. The goal is to eliminate redundant rows while retaining one row for each unique combination of primary key columns. However, the table contains some columns that are not relevant to the question and have slightly different data and must be ignored.

Solution:

For SQL Server 2005 and above, you can use the OVER() function and common table expressions (CTE) to efficiently remove duplicate rows.

CTE and DELETE statements:

<code class="language-sql">WITH cte AS (
  SELECT [foo], [bar], 
     row_number() OVER(PARTITION BY foo, bar ORDER BY baz) AS [rn]
  FROM TABLE
)
DELETE cte WHERE [rn] > 1</code>

Instructions:

• CTE creates a sorted subset of rows based on columns foo and bar.
• The ORDER BY baz clause is optional and allows ordering within each partition (primary key column group).
• The DELETE statement deletes all rows in the CTE with a rank greater than 1, effectively eliminating duplicates while retaining one row for each primary key column combination.

The above is the detailed content of How to Efficiently Delete Duplicate Rows in T-SQL Based on Key Columns?. For more information, please follow other related articles on the PHP Chinese website!