SQL Help

Delete duplicate rows in MySQL (safely)

Updated August 2026 · First published 2007 by David J. Lake

Duplicates creep in wherever a table was built without a unique constraint: imported spreadsheets, retried form submissions, inherited databases. Before deleting anything, two rules:

• Back up first. There is no undelete. A quick CREATE TABLE cars_backup AS SELECT * FROM cars; costs seconds.
• Decide which copy survives. "Any of them" and "the newest one" need slightly different queries.

Working example — a table with no primary key and some straight duplicates:

CREATE TABLE cars (
  id    INT AUTO_INCREMENT PRIMARY KEY,   -- if you don't have one, add it: it makes everything easier
  make  VARCHAR(20),
  model VARCHAR(20),
  year  SMALLINT
);

INSERT INTO cars (make, model, year) VALUES
('Ford','Focus',2022), ('Ford','Focus',2022), ('Ford','Focus',2022),
('Kia','Sportage',2025), ('Kia','Sportage',2025),
('Skoda','Octavia',2024);

Method 1: ROW_NUMBER() — the modern default

Number each row within its duplicate group, then delete everything that isn't row 1:

DELETE FROM cars
WHERE id IN (
  SELECT id FROM (
    SELECT id,
           ROW_NUMBER() OVER (
             PARTITION BY make, model, year
             ORDER BY id
           ) AS rn
    FROM cars
  ) AS ranked
  WHERE rn > 1
);

ORDER BY id keeps the oldest copy; use ORDER BY id DESC to keep the newest. Change the PARTITION BY columns to change what counts as "a duplicate". (The extra derived-table wrapping is needed because MySQL won't let a DELETE reference the same table in a plain subquery.)

Method 2: self-join delete — short and old-school

DELETE c2
FROM cars AS c1
JOIN cars AS c2
  ON  c2.make  = c1.make
  AND c2.model = c1.model
  AND c2.year  = c1.year
  AND c2.id    > c1.id;

Every duplicate row with a higher id than another identical row gets deleted — leaving exactly one (the lowest id) per group. Works on any MySQL version.

Method 3: rebuild and rename — safest for big cleanups

When the table is large or the stakes are high, don't delete in place. Build a clean copy, inspect it, then swap:

CREATE TABLE cars_clean LIKE cars;

-- the unique index does the de-duplicating for us
ALTER TABLE cars_clean ADD UNIQUE KEY uq_car (make, model, year);

INSERT IGNORE INTO cars_clean
SELECT * FROM cars;

-- eyeball cars_clean, count rows, run spot checks... then:
RENAME TABLE cars TO cars_old, cars_clean TO cars;

-- when you're confident:
DROP TABLE cars_old;

INSERT IGNORE silently skips any row that would violate the unique key, so only the first copy of each group makes it across. The rename step is atomic, and until you drop cars_old you have a full rollback path.

Keeping a specific row per group

Sometimes rows aren't identical — you want to keep, say, only the newest year per make and model, discarding older releases. That's the latest-row-per-group problem in delete form, and Method 1 handles it with one change: partition by the group, order by what makes a row the keeper:

...ROW_NUMBER() OVER (PARTITION BY make, model ORDER BY year DESC) AS rn...

Stop them coming back

Whichever method you use, finish the job — duplicates got in because nothing was stopping them:

ALTER TABLE cars ADD UNIQUE KEY uq_car (make, model, year);

Future duplicate inserts now fail loudly (or get skipped, if the application uses INSERT IGNORE / ON DUPLICATE KEY UPDATE deliberately), instead of silently polluting the table again.