To delete duplicate rows in MySQL but keep one, join the table to itself on the columns that define a duplicate and delete the row with the higher id: DELETE c1 FROM customers c1 JOIN customers c2 ON c1.email = c2.email AND c1.id > c2.id;. That keeps the oldest row (the lowest id) in each group and removes the rest. Before you run it, find the duplicates with SELECT email, COUNT(*) FROM customers GROUP BY email HAVING COUNT(*) > 1, back the table up, and run the delete inside a transaction. On MySQL 8 you can also use ROW_NUMBER() OVER (PARTITION BY ...), which makes it easy to keep the newest row instead. Afterwards, add a UNIQUE key so duplicates can't come back.
I tested every query in this guide on MySQL 8.4.6 (the current long-term support release) on 7 October 2026, using a small table of UK customer records with deliberate problems: exact repeats, a NULL postcode, and one email typed with capital letters and a trailing space. I also ran the key queries on MariaDB 11.8.6, because it gave different answers in two places, which I point out below. The output under each query is copied from the mysql client.
Here's the table and the data every example starts from. Each test reloads it, so the results don't depend on the order you read them in.
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(120) NOT NULL,
first_name VARCHAR(60) NOT NULL,
postcode VARCHAR(10) NULL,
created_at DATETIME NOT NULL
);
INSERT INTO customers (email, first_name, postcode, created_at) VALUES
('amira@example.co.uk', 'Amira', 'LS1 4AP', '2026-01-05 09:00:00'),
('tom@example.co.uk', 'Tom', 'M1 1AE', '2026-01-06 10:00:00'),
('amira@example.co.uk', 'Amira', 'LS1 4AP', '2026-02-11 14:20:00'),
('priya@example.co.uk', 'Priya', NULL, '2026-02-12 08:15:00'),
('Tom@Example.co.uk ', 'Tom', 'M1 1AE', '2026-03-01 12:00:00'),
('amira@example.co.uk', 'Amira', 'LS2 7HY', '2026-03-09 16:45:00'),
('priya@example.co.uk', 'Priya', NULL, '2026-04-02 11:30:00'),
('owen@example.co.uk', 'Owen', 'CF10 1EP','2026-04-03 09:05:00');
Look closely at row 5: 'Tom@Example.co.uk ' has capital letters and a space at the end. Row 4 and row 7 are Priya with a NULL postcode. Amira appears three times, twice at the same postcode and once at a different one. Real customer tables look like this, usually because a sign-up form was submitted twice or an import was run again.
Always find duplicates before deleting them. GROUP BY puts rows with the same value into one group, COUNT(*) counts each group, and HAVING filters the groups after counting (WHERE runs before grouping, so it can't see the count).
SELECT email, COUNT(*) AS copies
FROM customers
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY copies DESC;
+---------------------+--------+
| email | copies |
+---------------------+--------+
| amira@example.co.uk | 3 |
| priya@example.co.uk | 2 |
+---------------------+--------+
Amira has three rows and Priya has two. Tom isn't listed, because MySQL 8.4 decided tom@example.co.uk and Tom@Example.co.uk are different values. That surprised me less than what MariaDB did, which I cover in the section on case and spaces.
To treat rows as duplicates only when several columns all match, list every column in GROUP BY. GROUP_CONCAT() is handy here because it shows the ids in each group.
SELECT email, postcode, COUNT(*) AS copies, GROUP_CONCAT(id ORDER BY id) AS ids
FROM customers
GROUP BY email, postcode
HAVING COUNT(*) > 1;
+---------------------+----------+--------+------+
| email | postcode | copies | ids |
+---------------------+----------+--------+------+
| amira@example.co.uk | LS1 4AP | 2 | 1,3 |
| priya@example.co.uk | NULL | 2 | 4,7 |
+---------------------+----------+--------+------+
Now Amira's third row (postcode LS2 7HY) isn't a duplicate, so her group has two ids: 1 and 3. Priya's group has ids 4 and 7. Notice that GROUP BY puts the two NULL postcodes in the same group. Remember that, because a join behaves differently, as you'll see later.
Grouping hides the individual rows. To see every column of every duplicate, join the table to the list of duplicated values.
SELECT c.id, c.email, c.created_at
FROM customers c
JOIN (
SELECT email FROM customers GROUP BY email HAVING COUNT(*) > 1
) d ON d.email = c.email
ORDER BY c.email, c.id;
+----+---------------------+---------------------+
| id | email | created_at |
+----+---------------------+---------------------+
| 1 | amira@example.co.uk | 2026-01-05 09:00:00 |
| 3 | amira@example.co.uk | 2026-02-11 14:20:00 |
| 6 | amira@example.co.uk | 2026-03-09 16:45:00 |
| 4 | priya@example.co.uk | 2026-02-12 08:15:00 |
| 7 | priya@example.co.uk | 2026-04-02 11:30:00 |
+----+---------------------+---------------------+
This is the list to check by eye, or export to share with whoever owns the data. In a real project, this is the point where you ask "which copy is correct?". The oldest one, the newest one, or the one with the most complete details? The answer decides which delete query you use.
The self-join delete works on every MySQL version, including 5.7. It joins each row (c1) to every other row (c2) with the same email, and deletes c1 whenever a row with a lower id exists. The row with the lowest id has no lower partner, so it survives.
DELETE c1
FROM customers c1
JOIN customers c2
ON c1.email = c2.email
AND c1.id > c2.id;
SELECT ROW_COUNT() AS deleted;
SELECT id, email FROM customers ORDER BY id;
+---------+
| deleted |
+---------+
| 3 |
+---------+
+----+---------------------+
| id | email |
+----+---------------------+
| 1 | amira@example.co.uk |
| 2 | tom@example.co.uk |
| 4 | priya@example.co.uk |
| 5 | Tom@Example.co.uk |
| 8 | owen@example.co.uk |
+----+---------------------+
Three rows were deleted: Amira's ids 3 and 6, and Priya's id 7. Each email that was duplicated now has exactly one row, the oldest. Tom's two rows both survived, because MySQL still considers the emails different (I fix that later).
The key part is c1.id > c2.id. If you write c1.id <> c2.id instead, both copies of every pair match, and you delete every duplicated row, keeping none. Flip the comparison to c1.id < c2.id to keep the highest id (usually the newest row) instead. To match on more than one column, add each one to the ON clause: ON c1.email = c2.email AND c1.postcode = c2.postcode AND c1.id > c2.id.
On MySQL 8.0 and later, window functions make the "which one do we keep?" decision explicit. ROW_NUMBER() numbers the rows inside each group, in the order you choose. Here's a preview first, which is a safe SELECT:
SELECT id, email, created_at,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM customers
ORDER BY email, id;
+----+---------------------+---------------------+----+
| id | email | created_at | rn |
+----+---------------------+---------------------+----+
| 1 | amira@example.co.uk | 2026-01-05 09:00:00 | 1 |
| 3 | amira@example.co.uk | 2026-02-11 14:20:00 | 2 |
| 6 | amira@example.co.uk | 2026-03-09 16:45:00 | 3 |
| 8 | owen@example.co.uk | 2026-04-03 09:05:00 | 1 |
| 4 | priya@example.co.uk | 2026-02-12 08:15:00 | 1 |
| 7 | priya@example.co.uk | 2026-04-02 11:30:00 | 2 |
| 2 | tom@example.co.uk | 2026-01-06 10:00:00 | 1 |
| 5 | Tom@Example.co.uk | 2026-03-01 12:00:00 | 1 |
+----+---------------------+---------------------+----+
Every row with rn = 1 is the keeper; everything with rn > 1 is a duplicate. To keep the most recent row, order each group by created_at DESC and delete everything numbered above 1:
DELETE FROM customers
WHERE id IN (
SELECT id FROM (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC, id DESC) AS rn
FROM customers
) ranked
WHERE rn > 1
);
SELECT ROW_COUNT() AS deleted;
SELECT id, email, created_at FROM customers ORDER BY id;
+---------+
| deleted |
+---------+
| 3 |
+---------+
+----+---------------------+---------------------+
| id | email | created_at |
+----+---------------------+---------------------+
| 2 | tom@example.co.uk | 2026-01-06 10:00:00 |
| 5 | Tom@Example.co.uk | 2026-03-01 12:00:00 |
| 6 | amira@example.co.uk | 2026-03-09 16:45:00 |
| 7 | priya@example.co.uk | 2026-04-02 11:30:00 |
| 8 | owen@example.co.uk | 2026-04-03 09:05:00 |
+----+---------------------+---------------------+
This time Amira's newest row (id 6, March) and Priya's newest (id 7, April) were kept. I added id DESC as a tie-breaker, so two rows with exactly the same timestamp still give a predictable result. The extra SELECT id FROM (...) ranked wrapper isn't decoration. It's what stops MySQL's error 1093, explained next. The MySQL 8.4 manual's window function reference covers ROW_NUMBER() and its relatives RANK() and DENSE_RANK().
The most natural way to write "delete everything except the lowest id per email" is a subquery. MySQL rejects it.
DELETE FROM customers
WHERE id NOT IN (SELECT MIN(id) FROM customers GROUP BY email);
ERROR 1093 (HY000) at line 1: You can't specify target table 'customers' for update in FROM clause
MySQL won't let a DELETE or UPDATE read from the same table in a subquery. The standard fix is to wrap the subquery in a second one, which makes MySQL build a temporary result first:
DELETE FROM customers
WHERE id NOT IN (
SELECT keep_id FROM (SELECT MIN(id) AS keep_id FROM customers GROUP BY email) AS k
);
SELECT ROW_COUNT() AS deleted;
+---------+
| deleted |
+---------+
| 3 |
+---------+
Three rows deleted, the same result as the self-join. Don't confuse this with the NOT IN trap: if the inner query could ever return NULL, NOT IN matches nothing and deletes nothing. MIN(id) on a primary key can't be NULL, so it's safe here.
MariaDB is different. When I ran the exact same failing query on MariaDB 11.8.6, it ran without an error. MariaDB has allowed this pattern for years, so a query copied from a MariaDB tutorial may fail on MySQL. The double-wrapped version works on both, so I'd always write it that way.
All three methods removed the same three rows from the test table, so the choice comes down to clarity and what you need to keep:
c1.id > c2.id): the shortest, works on MySQL 5.7 and every MariaDB version, and is easy to extend to more columns. It can get slow on big tables with no index on the matching columns, because every row is compared with every other row in its group.ROW_NUMBER(): MySQL 8.0 and later. Best when "which row to keep" depends on something other than the id, such as the newest date or the most complete record, because the ORDER BY inside OVER (...) says it in plain words.NOT IN (MIN(id) ...): easy to read, but needs the extra wrapper on MySQL and only keeps the lowest or highest id.Whichever you choose, add an index on the columns you match on before running it on a large table. With an index on email, MySQL can look up each row's partners directly instead of scanning the whole table for every row, and EXPLAIN on the matching SELECT shows you whether the index is used.
Priya's two rows look identical, NULL postcode included. GROUP BY grouped them, but an equality join doesn't, because in SQL NULL = NULL isn't true, it's unknown.
SELECT COUNT(*) AS pairs_with_equals
FROM customers a JOIN customers b
ON a.email = b.email AND a.postcode = b.postcode AND a.id < b.id
WHERE a.email = 'priya@example.co.uk';
SELECT COUNT(*) AS pairs_with_null_safe
FROM customers a JOIN customers b
ON a.email = b.email AND a.postcode <=> b.postcode AND a.id < b.id
WHERE a.email = 'priya@example.co.uk';
+-------------------+
| pairs_with_equals |
+-------------------+
| 0 |
+-------------------+
+----------------------+
| pairs_with_null_safe |
+----------------------+
| 1 |
+----------------------+
With =, the join found zero duplicate pairs for Priya. With MySQL's null-safe operator <=>, it found one. So if you de-duplicate on a column that can be NULL, use <=> in the self-join, or your delete will quietly skip those rows.
Whether 'tom@example.co.uk' equals 'Tom@Example.co.uk ' isn't decided by MySQL in general. It's decided by the column's collation.
SELECT @@collation_database AS db_collation;
SELECT 'tom@example.co.uk' = 'Tom@Example.co.uk' AS case_insensitive,
'tom@example.co.uk' = 'tom@example.co.uk ' AS trailing_space_equal;
+--------------------+
| db_collation |
+--------------------+
| utf8mb4_0900_ai_ci |
+--------------------+
+------------------+----------------------+
| case_insensitive | trailing_space_equal |
+------------------+----------------------+
| 1 | 0 |
+------------------+----------------------+
MySQL 8.4's default collation, utf8mb4_0900_ai_ci, is case-insensitive (the ci), so capital letters don't matter. But it's a "NO PAD" collation, so the trailing space does make the values different. That's why Tom wasn't reported as a duplicate. On MariaDB 11.8.6, the same find.sql query listed Tom with two copies, because MariaDB's default collation ignores trailing spaces. Same data, same query, different answer. If you move between the two, check @@collation_database first.
The reliable fix is to clean the data, then de-duplicate:
SELECT LOWER(TRIM(email)) AS clean_email, COUNT(*) AS copies
FROM customers
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;
+---------------------+--------+
| clean_email | copies |
+---------------------+--------+
| amira@example.co.uk | 3 |
| tom@example.co.uk | 2 |
| priya@example.co.uk | 2 |
+---------------------+--------+
UPDATE customers SET email = LOWER(TRIM(email));
SELECT ROW_COUNT() AS cleaned;
DELETE FROM customers
WHERE id IN (
SELECT id FROM (
SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM customers
) ranked
WHERE rn > 1
);
SELECT ROW_COUNT() AS deleted;
SELECT id, email FROM customers ORDER BY id;
+---------+
| cleaned |
+---------+
| 1 |
+---------+
+---------+
| deleted |
+---------+
| 4 |
+---------+
+----+---------------------+
| id | email |
+----+---------------------+
| 1 | amira@example.co.uk |
| 2 | tom@example.co.uk |
| 4 | priya@example.co.uk |
| 8 | owen@example.co.uk |
+----+---------------------+
The UPDATE cleaned one email (Tom's), then the delete removed four rows and left one row per real customer. Fixing the stored values is better than just grouping on LOWER(TRIM(email)), because the next query someone writes won't know about the trick. Trimming and lower-casing should also happen when the data comes in, which is part of the validation I cover in my guide to PHP form validation and sanitisation.
DELETE can't be undone once it's committed, so make it reversible while you check the result. With InnoDB tables, a transaction lets you look before you commit:
CREATE TABLE customers_backup AS SELECT * FROM customers;
START TRANSACTION;
DELETE c1 FROM customers c1 JOIN customers c2
ON c1.email = c2.email AND c1.id > c2.id;
SELECT COUNT(*) AS rows_left FROM customers;
ROLLBACK;
SELECT COUNT(*) AS rows_after_rollback FROM customers;
DROP TABLE customers_backup;
+-----------+
| rows_left |
+-----------+
| 5 |
+-----------+
+---------------------+
| rows_after_rollback |
+---------------------+
| 8 |
+---------------------+
Inside the transaction, five rows were left. After ROLLBACK, all eight were back. In real use you'd run your checks, then COMMIT if the numbers match what the SELECT queries told you to expect. I also made a backup copy with CREATE TABLE ... AS SELECT, which is the cheapest insurance there is. On a large production table, take a proper dump with mysqldump and run the delete during a quiet period, because it locks rows as it goes.
On a table with millions of rows and a high share of duplicates, a big DELETE can run for a long time, hold locks and fill the undo log. It's often quicker and safer to copy the rows you want to keep into a new table that already has the unique key, then swap the tables over.
CREATE TABLE customers_clean LIKE customers;
ALTER TABLE customers_clean ADD UNIQUE KEY uq_email (email);
INSERT INTO customers_clean
SELECT id, email, first_name, postcode, created_at
FROM (
SELECT c.*, ROW_NUMBER() OVER (PARTITION BY LOWER(TRIM(email)) ORDER BY id) AS rn
FROM customers c
) ranked
WHERE rn = 1;
SELECT ROW_COUNT() AS copied;
RENAME TABLE customers TO customers_old, customers_clean TO customers;
SELECT id, email FROM customers ORDER BY id;
DROP TABLE customers_old;
+--------+
| copied |
+--------+
| 4 |
+--------+
+----+---------------------+
| id | email |
+----+---------------------+
| 1 | amira@example.co.uk |
| 2 | tom@example.co.uk |
| 4 | priya@example.co.uk |
| 8 | owen@example.co.uk |
+----+---------------------+
CREATE TABLE ... LIKE copies the structure, including the primary key and indexes. The INSERT ... SELECT copies only rows numbered 1 in each group, here grouping on LOWER(TRIM(email)) so Tom's two spellings count as one customer. Four rows were copied, one per customer. RENAME TABLE then swaps both tables in a single atomic step, so your application never sees a missing table. I kept the old table until the end and dropped it only after checking the result; in real life I'd keep it for a day or two.
Two cautions. First, anything written to the old table between the copy and the rename is lost, so either pause writes or run it in a maintenance window. Second, foreign keys from other tables point at the old table by name and follow it when it's renamed, so check information_schema.KEY_COLUMN_USAGE before using this approach on a table that other tables reference. For small tables like most contact lists or sign-up forms, the plain self-join delete is simpler and perfectly fine.
If the duplicates came from a spreadsheet or CSV import, it can be easier to fix them before they reach the database. My guide on how to remove duplicates from a list in Python covers de-duplicating rows by key, case-insensitively, before you load them.
Deleting duplicates fixes today. A unique index fixes tomorrow.
DELETE c1 FROM customers c1 JOIN customers c2
ON c1.email = c2.email AND c1.id > c2.id;
ALTER TABLE customers ADD UNIQUE KEY uq_customers_email (email);
INSERT INTO customers (email, first_name, created_at)
VALUES ('owen@example.co.uk', 'Owen', NOW());
ERROR 1062 (23000) at line 4: Duplicate entry 'owen@example.co.uk' for key 'customers.uq_customers_email'
Once uq_customers_email exists, MySQL refuses a second row with the same email and returns error 1062. You can't add the key while duplicates exist (that fails with the same error), which is why the cleanup comes first. Your application then needs to handle the error, or avoid it with an upsert:
DELETE c1 FROM customers c1 JOIN customers c2
ON c1.email = c2.email AND c1.id > c2.id;
ALTER TABLE customers ADD UNIQUE KEY uq_customers_email (email);
INSERT INTO customers (email, first_name, postcode, created_at)
VALUES ('owen@example.co.uk', 'Owen', 'CF11 9LJ', '2026-10-07 11:00:00')
ON DUPLICATE KEY UPDATE postcode = VALUES(postcode);
SELECT ROW_COUNT() AS affected;
INSERT IGNORE INTO customers (email, first_name, created_at)
VALUES ('owen@example.co.uk', 'Owen', '2026-10-07 11:05:00');
SHOW WARNINGS;
SELECT id, email, postcode FROM customers WHERE email = 'owen@example.co.uk';
+----------+
| affected |
+----------+
| 2 |
+----------+
+---------+------+-----------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------+
| Warning | 1062 | Duplicate entry 'owen@example.co.uk' for key 'customers.uq_customers_email' |
+---------+------+-----------------------------------------------------------------------------+
+----+--------------------+----------+
| id | email | postcode |
+----+--------------------+----------+
| 8 | owen@example.co.uk | CF11 9LJ |
+----+--------------------+----------+
INSERT ... ON DUPLICATE KEY UPDATE updated Owen's postcode instead of adding a row, and reported 2 affected rows (MySQL's way of saying "updated an existing row"). INSERT IGNORE skipped the duplicate and left a warning. Use INSERT IGNORE with care, though, because it also hides other problems such as invalid data. In PHP, send these queries with prepared statements, as in my post on PDO prepared statements, and catch the duplicate error code to show a friendly "that email is already registered" message.
GROUP BY ... HAVING and ROW_NUMBER() previews first, and write down how many rows you expect to remove.<> instead of > in the self-join. c1.id <> c2.id deletes every copy, including the one you meant to keep.DELETE raises error 1093 on MySQL, even if it worked on MariaDB.= never matches two NULLs. Use <=> for columns that can be empty.LOWER(TRIM()) if in doubt.ORDER BY. Two rows with the same created_at can swap places between runs. Add the primary key as the last sort column.START TRANSACTION so you can roll back.UNIQUE key afterwards. Without it, the duplicates are back after the next double-click on a form.Group by the column (or columns) that should be unique and keep groups with more than one row: SELECT email, COUNT(*) FROM customers GROUP BY email HAVING COUNT(*) > 1;. Add more columns to GROUP BY to find duplicates across several columns, and GROUP_CONCAT(id) to see which rows are involved.
Use a self-join and delete the row with the higher id: DELETE c1 FROM customers c1 JOIN customers c2 ON c1.email = c2.email AND c1.id > c2.id;. That keeps the lowest id in each group. On MySQL 8 you can also delete rows where ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) > 1, wrapped in a derived table.
Either flip the self-join to c1.id < c2.id, which keeps the highest id, or use ROW_NUMBER() ordered by created_at DESC, id DESC and delete everything numbered above 1. The ROW_NUMBER() version is clearer when "latest" means a date column rather than the id.
MySQL doesn't allow a DELETE to use the same table in a subquery of its WHERE clause. Wrap the subquery in another one: WHERE id NOT IN (SELECT keep_id FROM (SELECT MIN(id) AS keep_id FROM customers GROUP BY email) AS k). The self-join version avoids the problem entirely.
Add a unique index on the column or columns that must be unique, for example ALTER TABLE customers ADD UNIQUE KEY uq_customers_email (email);. MySQL will then reject duplicates with error 1062. Use INSERT ... ON DUPLICATE KEY UPDATE to update the existing row instead, and normalise values (trim and lower-case emails) before inserting.
If you're building the API that writes these rows, my PHP REST API tutorial shows how to return a clean 422 or 409 response when a duplicate is rejected.
// note
This is a learning note from studying the web. It is one small topic, written so I can remember it. It is not a course and not a claim that I have finished the subject.
If a sentence is wrong, say so from the contact page and name this title. Drafts never appear here. Related notes, when they exist, are other published posts, and the same sample rule applies to each of them.
usort($rows, fn($a, $b) => $a['grade'] <=> $b['grade']) sorts by one column. Descending, several columns, UK dates, case-insensitive names, keeping keys and array_multisort.
Two HTML elements give you a keyboard-friendly accordion with no JavaScript. Open one at a time with name, swap the arrow for +/−, animate it, and fix the screen reader trap.
structuredClone(obj) makes a true deep copy that keeps Dates, Maps, Sets and circular references. When spread, Object.assign and JSON.stringify break, and how to copy classes.