How to Find and Remove Duplicate Rows in MySQL

Duplicate rows creep in through double-clicked buttons, retried imports and missing constraints. Here's how to find them, clean them up and stop them coming back.

1. Find duplicates

SELECT email, COUNT(*) AS copies
FROM subscribers
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY copies DESC;

For duplicates across several columns, group by all of them: GROUP BY customer_id, product_id, DATE(created_at).

2. See the full rows

SELECT s.*
FROM subscribers s
JOIN (
  SELECT email FROM subscribers GROUP BY email HAVING COUNT(*) > 1
) d ON d.email = s.email
ORDER BY s.email, s.id;

3. Delete duplicates, keeping the oldest row

-- Preview first
WITH ranked AS (
  SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
  FROM subscribers
)
SELECT id FROM ranked WHERE rn > 1;

-- Then delete
DELETE s FROM subscribers s
JOIN (
  SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
  FROM subscribers
) ranked ON ranked.id = s.id
WHERE ranked.rn > 1;

Change the ORDER BY inside OVER to control which row survives, for example ORDER BY updated_at DESC to keep the newest.

Back up before deleting

CREATE TABLE subscribers_backup AS SELECT * FROM subscribers;

4. Prevent new duplicates

ALTER TABLE subscribers ADD UNIQUE KEY uq_subscribers_email (email);

-- Inserts that would duplicate now fail; or handle them gracefully:
INSERT INTO subscribers (email, name) VALUES ('asha@example.com', 'Asha')
ON DUPLICATE KEY UPDATE name = VALUES(name);

Watch out for "near" duplicates

• Case and spaces: Asha@Example.com vs asha@example.com. Normalise with LOWER(TRIM(email)) before comparing, and store normalised values.
• Collation: with the common case-insensitive collations, MySQL already treats differently-cased strings as equal in unique indexes.