How Do You Find and Delete Duplicate Rows in SQL?

The short answer

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
  FROM customers
)
DELETE FROM customers
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

Number every row within each group of duplicates, keep the row numbered 1, delete the rest. That's the whole idea. The hard part isn't the syntax, it's three decisions this one-liner hides: what counts as a "duplicate" in your table, which copy of each duplicate survives, and whether your database even lets you run a query shaped like this one. Get any of those wrong and the delete runs cleanly and quietly removes the wrong rows.

The rest of this post works through all three using a customers table with a planted duplicate: Kayla Perry signed up twice, two days apart, same email both times.

Every example below builds on this customers_with_dupe CTE rather than mutating the real customers table, so you can run every block in any order and get the same result. A real DELETE against your own table only needs the final section.

Step 1: decide what makes two rows "duplicates"

A duplicate isn't necessarily a row that's identical in every column. Row 16 above has a different id and signup_date than row 11, but it's the same person signing up twice. For this table, "duplicate" means "same email," not "same everything." Pick the wrong key and you'll either miss real duplicates (key too specific) or merge unrelated people who happen to share a value (key too loose). GROUP BY on that key, filtered with HAVING COUNT(*) > 1, finds every group with more than one row:

One group, two copies. That tells you duplicates exist and which email is affected, but not which specific rows to delete, that's next.

Step 2: preview the delete before you run it

Every guide on this topic agrees on the pattern: number the rows in each duplicate group with ROW_NUMBER(), then target everything numbered above

1. What most of them skip is running the numbered version as a plain SELECT first, so you can see exactly which rows a real DELETE would remove before any data disappears.

Both Kaylas get rn values, 1 and 2, purely from ROW_NUMBER() counting within the partition. Notice there's no reliance on a pre-existing "is this a duplicate" flag or a separate lookup table, the window function does the counting for you even though neither row knows it's a duplicate on its own. Filter to rn = 1 and you've previewed the survivors. Filter to rn > 1 and you've previewed exactly what a DELETE would remove:

Only id 16 shows up. That's your delete target, confirmed before you've deleted anything. For more on how ROW_NUMBER assigns values within a partition, see the ROW_NUMBER example.

Step 3: picking which copy survives is an ORDER BY, not an accident

ORDER BY id inside the window keeps the lowest id, which is usually the oldest row. That's a choice, not a default you're stuck with. If the rule is "keep whichever signup has the most complete data" or "keep the most recent," change the ORDER BY, not the filter:

Flip to signup_date DESC and id 16, the newer signup, becomes rn = 1 and survives instead, id 11 becomes the delete target. Whoever writes ORDER BY id without thinking about it is implicitly deciding "oldest wins" for every duplicate group in the table, which is fine if that's actually the rule, and silently wrong if it isn't.

The gotcha every ranking guide glosses over: NULL breaks the self-join version

ROW_NUMBER isn't the only way people dedupe. A common alternative is a self-join: join the table to itself on the duplicate key, keep the row with the smaller id, delete the other. It looks equivalent to the GROUP BY approach, and for non-NULL keys it is. It quietly stops working the moment the duplicate key can be NULL.

GROUP BY treats all NULLs in a column as one group, for grouping purposes only, SQL pretends NULL = NULL here even though = itself never does. A self-join's ON a.email = b.email uses real = comparison, where NULL = NULL evaluates to unknown, so two customers who both have a NULL email never match each other and never get caught:

GROUP BY correctly reports two rows sharing a NULL email as a group of 2. Now run the self-join a dedupe script would actually use to find the "other copy" of each row:

Zero rows. The self-join can't see the pair at all, because NULL = NULL is never true, so nothing joins. If a dedupe script is built around that join pattern, rows with a missing key sail through untouched every single time, and nothing about the result looks like an error, it's just empty. ROW_NUMBER() OVER (PARTITION BY email ...) doesn't have this problem: window partitioning groups NULLs together the same way GROUP BY does, so it catches this pair correctly. If you need the full background on why NULL behaves this way under =, see Understanding NULL.

The part that depends on your database: can you DELETE from the thing you're reading?

The DELETE ... WHERE id IN (SELECT id FROM ranked WHERE rn > 1) pattern from the top of this post works as-is on PostgreSQL, SQLite, and DuckDB. MySQL rejects a simpler-looking version of it, DELETE FROM customers WHERE id IN (SELECT id FROM customers WHERE ...) referencing the same table directly inside its own subquery, with "You can't specify target table for update in FROM clause." Wrapping the subquery in a CTE first, as above, or in a derived table (SELECT * FROM (SELECT ...) AS x), works around it on MySQL because the extra layer stops the optimizer from treating it as the same table reference. SQL Server has its own idiom for the same delete, a CTE followed directly by DELETE FROM ranked WHERE rn > 1, deleting straight through the CTE rather than against the base table by id.

None of this is exotic; it's the single most common reason a correct- looking dedupe query throws an error on one engine and runs fine on another, and it's also why copying a dedupe script from a blog post without checking which database it was written for is the easiest way to waste twenty minutes on an error message that has nothing to do with your actual logic.

Putting it together

For a real table, the full sequence is: group by the duplicate key to confirm duplicates exist and see how many, preview the ROW_NUMBER ranking filtered to rn > 1 and read every row it lists, and only then switch that SELECT to a DELETE, choosing the ORDER BY deliberately and checking the key column for NULLs if you were ever tempted to use a self-join instead. The GROUP BY lesson covers the grouping half of this in more depth, and self-join walks through that join pattern for cases where it's the right tool (it is, just not for NULL-able dedupe keys). If you want to practice the ROW_NUMBER pattern on a table without a planted example already set up for you, the practice problems are a good next stop.