How to Remove Duplicate Rows from MySQL Tables Without Using Temporary Tables?

Purging Duplicates from MySQL Tables without Temporary Tables

MySQL tables often contain duplicate records that can clutter the database and compromise data integrity. Deleting these duplicates is crucial for maintaining data accuracy and improving query performance. This article explores an efficient approach to eliminate duplicate records without using temporary tables.

Unique Index Creation

The simplest method involves creating a unique index on the columns that define uniqueness in the table. For the TableA table, the following index ensures that the combination of member_id, quiz_num, question_num, and answer_num is unique:

ALTER IGNORE TABLE `TableA` ADD UNIQUE INDEX (`member_id`, `quiz_num`, `question_num`, `answer_num`);

Once the index is created, MySQL automatically maintains its integrity, preventing duplicate records from being inserted.

Primary Key Deletion

Alternatively, adding a primary key to the table can also prevent duplicate insertions. The following query deletes duplicate records based on the primary key:

DELETE FROM member
WHERE id IN (
    SELECT *
    FROM (
        SELECT id
        FROM member
        GROUP BY member_id, quiz_num, question_num, answer_num
        HAVING (COUNT(*) > 1)
    ) AS A
);

The DELETE statement removes records based on the id column, ensuring that only one record with the same unique combination of columns remains.

The above is the detailed content of How to Remove Duplicate Rows from MySQL Tables Without Using Temporary Tables?. For more information, please follow other related articles on the PHP Chinese website!