Course: MySQL Training
Topic: Removing Duplicate Records Safely
Course Link: MySQL Training Course
Demo Link: Register for a Course Demo
Duplicate records are rows that repeat information already stored in a database according to a defined business rule. They can occur because of repeated form submissions, importing the same file more than once, incorrect application logic, or missing database constraints.
Duplicate data can cause inaccurate reports, incorrect totals, wasted storage, and inconsistent application behavior. For example, if the same customer order is inserted twice, a sales report may count the same purchase more than once.
Removing duplicates safely requires identifying the duplicate groups, deciding which record should remain, previewing the rows that may be deleted, backing up the data, and validating the result. Similar-looking rows are not necessarily duplicates: they may represent different people, transactions, or events.
GROUP BY and HAVING.A duplicate must be defined using the business meaning of the data. For example, two customer records with the same email may be duplicates if the business rule requires one customer per email. Two orders with the same total amount are not necessarily duplicates.
SELECT
email,
COUNT(*) AS occurrence_count
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;This query groups customers by email and returns values appearing in multiple rows. It identifies potential duplicates but does not delete anything.
SELECT
product_code,
warehouse_code,
COUNT(*) AS occurrence_count
FROM inventory
GROUP BY product_code, warehouse_code
HAVING COUNT(*) > 1;This query identifies repeated product-and-warehouse combinations. Include every column required by the duplicate definition; grouping by too few columns can incorrectly classify valid records as duplicates.
SELECT c.*
FROM customers AS c
JOIN (
SELECT email
FROM customers
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1
) AS d
ON c.email = d.email
ORDER BY c.email, c.customer_id;This query lists the individual rows whose non-null email appears more than once. Inspect their IDs, timestamps, and other details before choosing which record to retain.
DESCRIBE customers;
SHOW CREATE TABLE customers;Inspect the primary key, indexes, foreign keys, and storage engine. A stable primary key allows you to identify individual records even when other column values match.
CREATE TABLE customers_backup_20261017 AS
SELECT *
FROM customers;This creates a copy of the selected table data, but it does not automatically reproduce all original indexes, constraints, triggers, or foreign-key definitions. For production data, use a verified backup procedure and confirm that the data can be restored.
Possible retention rules include keeping the smallest ID, the newest record, the most complete record, or the record confirmed as authoritative. Do not assume the smallest ID is always correct. Check whether other tables reference the records and whether related rows need to be reassigned before deletion.
A self-join compares rows in the same table. If the approved rule is to keep the smallest customer ID for each non-null email, rows with higher IDs can be identified as deletion candidates.
SELECT
c1.customer_id,
c1.name,
c1.email
FROM customers AS c1
JOIN customers AS c2
ON c1.email = c2.email
AND c1.customer_id > c2.customer_id
WHERE c1.email IS NOT NULL
ORDER BY c1.email, c1.customer_id;The condition c1.customer_id > c2.customer_id selects rows with a higher ID than another row sharing the same email. Review this result before running a deletion.
DELETE c1
FROM customers AS c1
JOIN customers AS c2
ON c1.email = c2.email
AND c1.customer_id > c2.customer_id
WHERE c1.email IS NOT NULL;Warning: This is a destructive query. Use it only when email is the intended duplicate key and keeping the smallest ID is the approved rule. Preview the rows, verify the backup, and check foreign-key dependencies first.
MySQL 8.0 and later support window functions such as ROW_NUMBER(). This function assigns a number to each row within a group, making it easier to identify extra copies.
SELECT
customer_id,
name,
email,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY customer_id
) AS row_num
FROM customers
WHERE email IS NOT NULL;Rows are grouped by email. The smallest customer ID receives row number 1, and additional rows receive higher numbers.
WITH ranked_customers AS (
SELECT
customer_id,
name,
email,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY customer_id
) AS row_num
FROM customers
WHERE email IS NOT NULL
)
SELECT *
FROM ranked_customers
WHERE row_num > 1
ORDER BY email, customer_id;This query identifies deletion candidates without modifying the original table.
One controlled approach is to store the candidate IDs in a temporary table, inspect them, and then delete those IDs.
CREATE TEMPORARY TABLE duplicate_customer_ids AS
WITH ranked_customers AS (
SELECT
customer_id,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY customer_id
) AS row_num
FROM customers
WHERE email IS NOT NULL
)
SELECT customer_id
FROM ranked_customers
WHERE row_num > 1;
SELECT c.*
FROM customers AS c
JOIN duplicate_customer_ids AS d
ON c.customer_id = d.customer_id
ORDER BY c.email, c.customer_id;After confirming the candidates and handling related records, perform the deletion in a transaction when supported:
START TRANSACTION;
DELETE c
FROM customers AS c
JOIN duplicate_customer_ids AS d
ON c.customer_id = d.customer_id;
SELECT ROW_COUNT() AS deleted_rows;
SELECT email, COUNT(*) AS occurrence_count
FROM customers
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;
-- Commit only if validation succeeds.
COMMIT;
-- If validation fails before commit, execute ROLLBACK instead.Run these statements in the same database session. Do not commit until the results have been checked. Rollback depends on the storage engine and operations involved; a committed deletion generally requires a backup or recovery procedure.
MySQL groups null values together in GROUP BY, but ordinary equality comparisons do not match one null value to another. This is often appropriate when a missing email alone does not prove that two records are duplicates.
MySQL's null-safe equality operator <=> treats two null values as equal:
SELECT c1.customer_id, c2.customer_id
FROM customers AS c1
JOIN customers AS c2
ON c1.email <=> c2.email
AND c1.customer_id > c2.customer_id;Use this comparison only if the business rule explicitly treats null values as matching. Otherwise, unrelated records with missing emails may be incorrectly classified as duplicates.
SELECT
LOWER(TRIM(email)) AS normalized_email,
COUNT(*) AS occurrence_count
FROM customers
WHERE email IS NOT NULL
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;This query identifies potential duplicate groups after trimming spaces and converting text to lowercase. It is diagnostic only: confirm the appropriate normalization and comparison rules before deleting any rows.
Cleaning existing duplicates does not stop new duplicates from appearing. If a column or combination of columns must be unique, enforce that requirement in the database after resolving existing conflicts.
ALTER TABLE customers
ADD CONSTRAINT uq_customers_email UNIQUE (email);This creates a unique constraint on email. Existing conflicting values must be resolved first. MySQL unique indexes generally allow multiple null values, so this constraint does not require every customer to provide an email.
ALTER TABLE inventory
ADD CONSTRAINT uq_product_warehouse
UNIQUE (product_code, warehouse_code);This prevents duplicate product-and-warehouse combinations when that combination must be unique. Confirm the business rule, nullability, and existing data before adding the constraint.
| Problem | Possible Cause | Solution |
|---|---|---|
| Too many rows deleted | Incorrect duplicate definition or broad join condition. | Roll back before committing if possible; review the selection rule and restore from backup if needed. |
| Duplicates remain | Incorrect grouping columns or unhandled normalization differences. | Re-run duplicate checks and inspect remaining rows. |
| Unique constraint fails | Existing values conflict. | Resolve conflicts and verify the uniqueness rule. |
| Foreign-key error | Other rows reference a record being deleted. | Inspect dependent rows and handle relationships before deletion. |
| ROW_NUMBER() fails | Unsupported server version or invalid SQL syntax. | Check the MySQL version and use a compatible query. |
| Cleanup is slow | Large table or inefficient query plan. | Use EXPLAIN, evaluate indexes, and consider controlled batches. |
| Duplicates return | Application logic still permits repeated inserts. | Add appropriate uniqueness constraints and improve retry handling. |
CREATE TABLE customers_practice (
customer_id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(150)
) ENGINE=InnoDB;INSERT INTO customers_practice
(customer_id, name, email)
VALUES
(101, 'Asha', '[email protected]'),
(108, 'Asha', '[email protected]'),
(115, 'Ravi', '[email protected]'),
(120, 'Ravi', '[email protected]'),
(130, 'Neha', '[email protected]'),
(140, 'Unknown', NULL),
(141, 'Unknown', NULL);SELECT email, COUNT(*) AS occurrence_count
FROM customers_practice
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;SELECT c1.*
FROM customers_practice AS c1
JOIN customers_practice AS c2
ON c1.email = c2.email
AND c1.customer_id > c2.customer_id
WHERE c1.email IS NOT NULL
ORDER BY c1.email, c1.customer_id;The expected candidates are customer IDs 108 and 120 if the smallest ID is retained for each non-null email.
START TRANSACTION;
DELETE c1
FROM customers_practice AS c1
JOIN customers_practice AS c2
ON c1.email = c2.email
AND c1.customer_id > c2.customer_id
WHERE c1.email IS NOT NULL;
SELECT ROW_COUNT() AS deleted_rows;
SELECT email, COUNT(*) AS occurrence_count
FROM customers_practice
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;
-- Commit after validating the result.
COMMIT;For this example, two rows should be deleted and the duplicate-email check should return no rows. In production, always inspect the results before committing. The two null-email rows remain because missing email alone is not treated as proof of duplication.
Use GROUP BY on the columns that define duplicates and HAVING COUNT(*) > 1 to return repeated groups.
SELECT DISTINCT removes repeated combinations from a query result, while DELETE removes stored rows from a table.
A primary key uniquely identifies each row, allowing the query to target specific records even when other column values match.
It compares rows in the same table and identifies higher-key rows sharing the duplicate values of another row. The deletion condition must match the approved retention rule.
It ranks rows within each duplicate group so that the first row can be retained and later rows can be reviewed as deletion candidates.
A transaction can allow supported changes to be rolled back before committing if validation reveals a problem.
Use appropriate unique constraints, validate incoming data, and make application retry logic safe.
GROUP BY and HAVING COUNT(*) > 1 identify duplicate groups.SELECT query previews candidate rows.ROW_NUMBER() is available in MySQL 8.0 and later.Removing duplicate records safely in MySQL requires a precise duplicate definition, careful inspection, a verified backup, and a deliberate retention rule. Self-joins and window functions can identify duplicate rows, while transactions and post-cleanup validation help reduce the risk of accidental data loss.
After cleaning the existing data, consider adding appropriate unique constraints and improving application insert logic. Combining careful SQL, verified business rules, and preventive database design helps maintain accurate and reliable data.
