How to Remove Duplicate Records in MySQL Without Using Temporary Tables?

Removing Duplicate Records Without Temp Tables

When faced with duplicate records in a MySQL table, you may encounter situations where temp tables prove impractical or undesirable. This article explores alternative solutions for eliminating duplicates while retaining a single instance of each unique record.

One approach is to leverage Unique Indexes. By adding a unique index on the columns that uniquely identify records (member_id, quiz_num, question_num, answer_num), you prevent the insertion of duplicate rows, ensuring only one row exists for each set of unique values.

ALTER IGNORE TABLE TableA   <br>ADD UNIQUE INDEX (member_id, quiz_num, question_num, answer_num);<br>

Alternatively, you can add a primary key to the table and utilize a query to selectively delete duplicate records:

DELETE FROM member  <br>WHERE id IN (SELECT *</p>
<pre class="brush:php;toolbar:false">         FROM (SELECT id FROM member 
               GROUP BY member_id, quiz_num, question_num, answer_num HAVING (COUNT(*) > 1)
              ) AS A
        );

By using the COUNT(*) > 1 condition within the subquery, the query identifies rows that appear more than once within each unique combination of member_id, quiz_num, question_num, and answer_num. The id column of these duplicate rows is then used to locate and delete them from the member table.

These approaches provide efficient and reliable methods for removing duplicate records without relying on temp tables, ensuring data integrity and minimizing the impact on performance.

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