How do you delete duplicate rows from a table while keeping one row per duplicate group?

## Decide the two rules first "Remove the duplicates" is underspecified until you answer two questions: 1. **Duplicate on what?** Rarely on every column — usually on a business key like `email`, or `(customer_id, order_date, amount)`. 2. **Which row survives?** Any deterministic rule will do, but it must be deterministic: smallest surrogate id, earliest `created_at`, the row with the most non-NULL columns. "Any one of them" is not a rule a `DELETE` can express, and a non-deterministic tiebreaker means re-running the statement can keep a different row. An interviewer is usually watching for whether you ask these before writing SQL. ## The self-referencing EXISTS form The cleanest portable spelling deletes every row that has a *better* twin: ```sql DELETE FROM contacts c WHERE EXISTS ( SELECT 1 FROM contacts k WHERE k.email = c.email AND k.id < c.id ); ``` Read it as: "remove this row if some other row shares its email and has a smaller id." For a group of ids 1, 2, 3 sharing an email, rows 2 and 3 each have a smaller-id twin and go; row 1 has none and stays. Exactly one survivor per group, and the same survivor every time you run it. Swap `<` for `>` to keep the largest id, or correlate on a timestamp to keep the newest — with a second comparison as a tiebreaker if timestamps can tie. Engines that support window functions offer a ranking-based route as well; the aggregate and self-join forms shown here work everywhere. ## The aggregate form ```sql DELETE FROM contacts WHERE id NOT IN (SELECT MIN(id) FROM contacts GROUP BY email); ``` This computes the survivor set once — the minimum id per email — and deletes everything else. It is equally correct and reads well. Note that `MIN(id)` is never NULL for a non-empty group, so the usual `NOT IN` hazard around NULLs in the subquery result does not arise here. ## Two traps worth knowing **Reading the table you are deleting from.** Some engines — MySQL is the well-known case, with its "You can't specify target table ... for update in FROM clause" error — refuse a subquery in the `FROM` clause that reads the table being modified. The standard workaround is to wrap the subquery one level deeper so the engine materialises it first: ```sql DELETE FROM contacts WHERE id NOT IN ( SELECT keep_id FROM (SELECT MIN(id) AS keep_id FROM contacts GROUP BY email) AS survivors ); ``` That extra `SELECT ... FROM (...) AS survivors` is not decoration; it is what makes the statement run on those engines. **Deleting a whole group by accident.** A very common wrong answer is: ```sql -- WRONG: removes every row of every duplicated email, keeping none DELETE FROM contacts WHERE email IN (SELECT email FROM contacts GROUP BY email HAVING COUNT(*) > 1); ``` The subquery identifies *which emails* are duplicated, not *which rows* to drop, so all copies disappear — including the one you meant to keep. Finding the duplicated keys and choosing survivors are two different steps. ## When the rows have no identifier at all If the table has no primary key and rows are identical in every column, no `WHERE` clause can distinguish copy #1 from copy #2 — a predicate that is true for one is true for all. Standard SQL has no answer here; engines expose non-standard physical row identifiers that you can use, and the portable route is to rebuild: ```sql CREATE TABLE contacts_dedup AS SELECT DISTINCT * FROM contacts; -- verify counts, then swap the tables ``` That is also the pragmatic choice when the duplicate share is enormous: writing the survivors is cheaper than deleting the rest. ## NULLs in the key Be explicit about NULL keys. `k.email = c.email` is UNKNOWN when both emails are NULL, so the self-join form treats NULL-keyed rows as non-duplicates and keeps all of them; the `GROUP BY` form treats all NULL emails as one group and keeps one. Decide which behaviour you want before running either statement. ## Before you run it Convert the statement to `SELECT COUNT(*)` with the identical `FROM`/`WHERE`, compare it against `COUNT(*) - COUNT(DISTINCT email)` — those two numbers should agree — and run the real statement inside a transaction so an unexpected count can be rolled back. Deduplication is usually followed by adding the `UNIQUE` constraint that should have been there all along.