Finding and Deleting Duplicates Without Losing the Original

Four rows, two customers. One wrong statement removes both. Here is how to delete safely.

The problem

The users table has duplicate emails from multiple import sources. You need to know which emails repeat, and remove the extras.

Step 1 — find the duplicated values

SELECT email, COUNT(*) AS times
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

This is a fixed pattern: group by the column you are checking, keep groups with more than one row.

Step 2 — see the full duplicate rows

SELECT *
FROM users
WHERE email IN (
  SELECT email FROM users
  GROUP BY email HAVING COUNT(*) > 1
)
ORDER BY email, id;

Step 3 — delete, keeping one

Option 1: keep the lowest id

DELETE FROM users
WHERE id NOT IN (
  SELECT MIN(id) FROM users GROUP BY email
);

Option 2: ROW_NUMBER — more flexible

WITH numbered AS (
  SELECT id,
    ROW_NUMBER() OVER (
      PARTITION BY email ORDER BY created_at ASC, id ASC
    ) AS rn
  FROM users
)
DELETE FROM users
WHERE id IN (SELECT id FROM numbered WHERE rn > 1);

This version lets you decide which copy survives — the oldest, the most complete, whatever your criterion is.

Common mistakes

1. Matching on unnormalised data

'An@X.com ' and 'an@x.com' are different values. Group on LOWER(TRIM(email)) to catch them all.

2. Forgetting MySQL cannot DELETE from the table it is selecting

-- Error 1093 on MySQL
DELETE FROM users WHERE id NOT IN (SELECT MIN(id) FROM users GROUP BY email);

-- Wrap one level deeper to work around it
DELETE FROM users WHERE id NOT IN (
  SELECT * FROM (SELECT MIN(id) FROM users GROUP BY email) t
);

3. Not preventing the next round

CREATE UNIQUE INDEX ux_users_email ON users(LOWER(email));

Clean the data without adding a constraint and you will be doing this again next week.

Practice

• LeetCode 182 — Duplicate Emails
• LeetCode 196 — Delete Duplicate Emails
• LeetCode 1050 — Actors and Directors Who Cooperated At Least Three Times
• LeetCode 1729 — Find Followers Count