PrismitaLEARN · PRACTICE · GROW

How do you delete duplicate rows, keeping one copy?

SQL Server T-SQL Intermediate 1–2 Yrs2–3 Yrs SQL Query 1 min read

Quick Answer

Use ROW_NUMBER() in a CTE partitioned by the duplicate key, then DELETE FROM cte WHERE rn > 1. SQL Server lets you DELETE directly from a CTE, which removes the corresponding base-table rows. The ORDER BY in the window picks which copy survives.

I number the rows with ROW_NUMBER() partitioned by whatever makes them duplicates, inside a CTE, and then DELETE FROM the_cte WHERE rn > 1 — SQL Server propagates that delete to the base table. The ORDER BY in the window decides which one I keep, usually the lowest id or the most complete record. I'd run it in a transaction, check the row count first, and afterwards add a unique constraint so the duplicates can't come back. On a large table I'd batch it in chunks.

WITH D AS (
    SELECT *, ROW_NUMBER() OVER (
        PARTITION BY Email
        ORDER BY Id) AS rn
    FROM Users
)
DELETE FROM D WHERE rn > 1;
  • Take a backup / run in a transaction and check the count first.
  • Add every column that defines a “true duplicate” to the PARTITION BY.
  • Afterwards, add a UNIQUE constraint / index so it can't happen again.
  • For huge tables, batch the delete (DELETE TOP (10000) ... in a loop) to control the log and locking.
All SQL Server questions More on T-SQL