Find and delete duplicate rows while keeping exactly one copy

Question

Find and delete duplicate rows while keeping exactly one copy — the safe production way

Accepted Answer

97
   Find and delete duplicate rows while keeping exactly one copy
  Level: Hard — A real data-cleanup task; the "delete but keep one" twist and self-reference rule trip people up.

  
     Scenario: A bug caused the users table to have duplicate rows with the same email. You must delete the duplicates but keep one row per email (the oldest/lowest id), then add a UNIQUE constraint so it never happens again. Do it safely on a live table.
  

   Sample Data — users (with duplicates)
  
    
      
        idemailname
      
      
        1rumman@x.comRumman
        2krushna@x.comKrushna
        3rumman@x.comRumman (dup)
        4rumman@x.comRumman (dup)
        5krushna@x.comKrushna (dup)
      
    
  
  Goal: keep ids 1 and 2 (lowest per email); delete ids 3, 4, 5.

   Step 1 — First, FIND the Duplicates
-- Which emails are duplicated, and how many times?
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
-- rumman@x.com -> 3,  krushna@x.com -> 2

  
     The MySQL trap: You cannot directly DELETE FROM users WHERE id NOT IN (SELECT MIN(id) FROM users GROUP BY email) — MySQL throws "You can't specify target table 'users' for update in FROM clause." You can't read from and delete the same table in one subquery. You must wrap it in a derived table.
  

   Solution A — Self-JOIN Delete (keeps the lowest id)
-- Delete any row that has a "twin" with a smaller id
DELETE u1
FROM users u1
INNER JOIN users u2
    ON u1.email = u2.email      -- same email
   AND u1.id    > u2.id;        -- u1 is the "later" duplicate
-- Keeps the lowest id per email; deletes the rest

   Solution B — Window Function (MySQL 8.0+, most readable)
-- Rank duplicates within each email, then delete rank > 1
DELETE FROM users
WHERE id IN (
    SELECT id FROM (
        SELECT id,
               ROW_NUMBER() OVER (
                   PARTITION BY email ORDER BY id
               ) AS rn
        FROM users
    ) t
    WHERE rn > 1        -- everything except the first (rn = 1) is a duplicate
);
-- The extra derived table (t) avoids the "can't target same table" error

   Solution C — Safest for Production (create-and-swap)
-- Zero-lock-risk approach on huge tables: rebuild clean, then swap
CREATE TABLE users_clean LIKE users;

INSERT INTO users_clean
SELECT * FROM users
WHERE id IN (
    SELECT MIN(id) FROM users GROUP BY email   -- keep one per email
);

-- Swap the tables atomically
RENAME TABLE users TO users_old, users_clean TO users;
-- Verify, then: DROP TABLE users_old;

   Step 3 — Prevent It Forever
-- Add a UNIQUE constraint so duplicates can never return
ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);
-- (This will fail if duplicates still exist — a good safety check)

  
     Always back up first: Before any bulk DELETE, take a backup or copy the to-be-deleted rows into an archive table. Run the SELECT version of your filter first to preview exactly what will be deleted — deletes are irreversible without a backup.
  

  
     Interviewer follow-up: "What if 'duplicate' means all columns match, not just email?" → GROUP BY / PARTITION BY on all the relevant columns. And if there's no primary key to break ties, add a temporary auto-increment column first so you have a unique id to keep/delete by.

Find and delete duplicate rows while keeping exactly one copy — the safe production way

Answer with Explanation

Sample Data — `users` (with duplicates)

Step 1 — First, FIND the Duplicates

Solution A — Self-JOIN Delete (keeps the lowest id)

Solution B — Window Function (MySQL 8.0+, most readable)

Solution C — Safest for Production (create-and-swap)

Step 3 — Prevent It Forever

id	email	name
1	rumman@x.com	Rumman
2	krushna@x.com	Krushna
3	rumman@x.com	Rumman (dup)
4	rumman@x.com	Rumman (dup)
5	krushna@x.com	Krushna (dup)