How Can I Efficiently Remove Duplicate Records from a MySQL Table Without Using Temporary Tables?

Efficient Removal of Duplicate Records in MySQL without Temp Tables

Discovering duplicate records in a database can be a challenge, especially without a primary key. However, there are effective techniques to address this issue.

In our case, we have a table called TableA that contains user responses to questionnaires. Due to an inadvertent error, some users have submitted duplicate responses. Our objective is to remove these duplicates while ensuring that one remaining row persists.

Solution 1: Unique Index

One approach is to add a unique index on the relevant columns in TableA. This will enforce the uniqueness of these fields, preventing the insertion of additional duplicates.

To achieve this, execute the following query:

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

Solution 2: Primary Key and Deletion

Alternatively, we can create a primary key on the table and utilize a specific query to identify and remove duplicates.

To create a primary key, we can use this query:

ALTER TABLE `TableA` ADD PRIMARY KEY (`member_id`, `quiz_num`, `question_num`, `answer_num`);

Once the primary key is in place, we can delete duplicates using this query:

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 above is the detailed content of How Can I Efficiently Remove Duplicate Records from a MySQL Table Without Using Temporary Tables?. For more information, please follow other related articles on the PHP Chinese website!