Delete Duplicate Rows in MySQL and Keep One Record

To delete duplicate rows in MySQL while keeping one record, compare the columns that define a duplicate and choose which row to retain. A primary key such as customer_id distinguishes rows; it is not usually part of the duplicate check. The example below treats rows with the same non-NULL email as duplicates and keeps the row with the lowest customer_id. For read-only ways to find duplicates first, see 4 ways to find duplicate rows in MySQL.

This method assumes the table uses InnoDB so that the delete can be rolled back. Check the table definition before running a data-changing statement:

SHOW CREATE TABLE customers;

1. Preview the rows that would be deleted

The self-join pairs each row with an older keeper row that has the same email. DISTINCT prevents an extra row from appearing more than once in the preview when an email occurs three or more times:

SELECT DISTINCT
    duplicate.customer_id,
    duplicate.email
FROM customers AS duplicate
JOIN customers AS keeper
    ON keeper.email = duplicate.email
   AND keeper.customer_id < duplicate.customer_id
WHERE duplicate.email IS NOT NULL
ORDER BY duplicate.email, duplicate.customer_id;

Review this result before deleting anything. If the duplicate key is a combination of columns, compare all of them in the ON clause. For example, add AND keeper.region_id = duplicate.region_id if an email is only considered duplicate within the same region.

String equality follows the column’s collation. With a case-insensitive collation, values such as [email protected] and [email protected] can compare equal. Check the column definition with SHOW FULL COLUMNS FROM customers and make sure that its collation matches your application’s duplicate rules. See MySQL’s character comparison rules.

The example excludes NULL emails. If NULL should count as the same duplicate key, use MySQL’s null-safe equality operator (<=>) for the email comparison and remove the WHERE duplicate.email IS NOT NULL condition. MySQL otherwise does not match NULL to NULL with =.

2. Delete the extra rows in a transaction

When the preview is correct, start a transaction and run a multiple-table DELETE. It removes each duplicate row once, even if it matches more than one keeper row:

START TRANSACTION;

DELETE duplicate
FROM customers AS duplicate
JOIN customers AS keeper
    ON keeper.email = duplicate.email
   AND keeper.customer_id < duplicate.customer_id
WHERE duplicate.email IS NOT NULL;

SELECT ROW_COUNT() AS rows_deleted;

The DELETE duplicate FROM ... JOIN ... form deletes from the named alias and uses the joined table only to identify matches. See the MySQL DELETE statement reference.

3. Verify, then commit or roll back

Before committing, check whether duplicate email groups remain:

SELECT email, COUNT(*) AS occurrences
FROM customers
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;

If the deleted-row count and remaining groups match your expectations, make the change permanent:

COMMIT;

If the result is unexpected, undo the transaction instead:

ROLLBACK;

MySQL runs in autocommit mode by default. START TRANSACTION keeps the InnoDB changes pending until COMMIT or ROLLBACK. A rollback cannot undo changes to nontransactional tables, so do not rely on this workflow for MyISAM. See the MySQL transaction control reference.

4. Prevent the duplicate from returning

If each non-NULL email must be unique, add a unique index after the cleanup transaction has been committed:

ALTER TABLE customers
ADD UNIQUE KEY uq_customers_email (email);

MySQL rejects later inserts or updates that repeat an existing non-NULL email. A unique index allows multiple NULL values, so make the column NOT NULL as well if every customer must have an email. Read the unique index rules and check existing application data before adding the constraint.

If rows reference one another through foreign keys, check those constraints and any ON DELETE actions before deleting duplicates. For a different retention rule, change the comparison so it keeps the row you selected during the preview; do not assume the lowest primary key is always the best record.