MySQL - Delete Duplicate Records

MySQL - Deleting Duplicate Records

What Constitutes a Duplicate Record?

Duplicate records occur when multiple rows in a table contain identical values for one or more key business columns (e.g., same email address, same product SKU). Data redundancy can arise from application bugs, data import errors, or lack of proper database constraints. Deleting duplicates is crucial for data integrity, accurate reporting, and optimal database performance.

Example Setup

Let’s create a contacts table where duplicate entries are possible. Note that the id is a unique primary key, but the business data (first_name, last_name, email) can be duplicated.

CREATE TABLE contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

-- Insert records, including duplicates for 'john.doe@example.com'
INSERT INTO contacts (first_name, last_name, email) VALUES
('John', 'Doe', 'john.doe@example.com'),      -- Keep this one
('Jane', 'Smith', 'jane.smith@example.com'),
('John', 'Doe', 'john.doe@example.com'),      -- Duplicate
('Peter', 'Jones', 'peter.jones@example.com'),
('John', 'Doe', 'john.doe@example.com');      -- Duplicate

Step 1: Identifying Duplicate Records

Before deleting anything, you must first identify which records are duplicates. The GROUP BY clause combined with the COUNT() function is perfect for this. We group by the columns that define a duplicate and count the occurrences.

SELECT
    email, -- The column(s) that define a duplicate
    COUNT(email) AS occurrences
FROM
    contacts
GROUP BY
    email
HAVING
    COUNT(email) > 1;

This query will show us which emails appear more than once.

email	occurrences
john.doe@example.com	3

Step 2: Deleting Duplicate Records

Once duplicates are identified, you can remove them. The goal is to keep one ‘original’ record and delete the rest. Here are two common methods.

Method A: Using a CTE with ROW_NUMBER() (Recommended)

This modern approach (available in MySQL 8.0+) uses a window function, ROW_NUMBER(), within a Common Table Expression (CTE). It’s clean, readable, and powerful. We partition the data by the duplicate-defining column(s) and assign a sequential number to each row within the partition. Any row with a number greater than 1 is a duplicate.

WITH NumberedContacts AS (
    SELECT
        id,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) as row_num
    FROM
        contacts
)
DELETE FROM contacts
WHERE id IN (
    SELECT id FROM NumberedContacts WHERE row_num > 1
);

Explanation:

1. WITH NumberedContacts AS (...): Defines a temporary, named result set (a CTE).
2. ROW_NUMBER() OVER (...): For each email group (PARTITION BY email), it numbers the rows starting from 1. We ORDER BY id to ensure the row with the lowest id is considered the original (gets row_num = 1).
3. DELETE ... WHERE id IN (...): The main DELETE statement then removes all rows whose id corresponds to a row_num greater than 1.

After running this, two rows will be deleted. You can verify with SELECT * FROM contacts;.

Method B: Using DELETE with a Self-JOIN (Classic Method)

This method works on older MySQL versions and uses a DELETE statement with an INNER JOIN on the same table. The join condition finds pairs of rows with the same email but different ids. The WHERE clause ensures that we delete the row with the higher id.

-- Note: Run this on the original table with duplicates, not after using Method A.
DELETE t1 FROM contacts t1
INNER JOIN contacts t2
WHERE
    t1.id > t2.id AND
    t1.email = t2.email;

This statement is concise but can be less intuitive than the CTE method for complex duplicate definitions (multiple columns).

Step 3: Preventing Future Duplicates

The best strategy is to prevent duplicates at the database level. After cleaning your data, add a UNIQUE constraint to the column(s) that should not have duplicates.

-- This will fail if duplicates still exist.
ALTER TABLE contacts
ADD CONSTRAINT uq_email UNIQUE (email);

With this constraint in place, any future INSERT or UPDATE that tries to create a duplicate email will be rejected by the database with an error, ensuring data integrity.

Safety First: Using Transactions and Backups

Performing mass deletions is a high-risk operation. Always follow these safety protocols:

1.  **Backup:** Before running any `DELETE` statement, create a backup of your table.
    `CREATE TABLE contacts_backup LIKE contacts;`
    `INSERT INTO contacts_backup SELECT * FROM contacts;`

2.  **Use Transactions:** Wrap your `DELETE` statement in a transaction. This allows you to preview the changes and `ROLLBACK` if something goes wrong.
    `START TRANSACTION;`
    `-- Your DELETE statement here...`
    `-- Verify the result with a SELECT statement.`
    `SELECT * FROM contacts;`
    `-- If correct, commit the changes.`
    `COMMIT;`
    `-- If incorrect, undo the changes.`
    `-- ROLLBACK;`

The client program examples have been removed as this task is primarily an administrative one, best performed directly with SQL for safety and control. Modern applications typically enforce uniqueness at the point of data entry.