How do I delete all the duplicate records in a MyS

Question

I've seen a number of variations on this but nothing quite matches what I'm trying to accomplish.

I have a table, TableA, which contain the answers given by users to configurable questionnaires. The columns are member_id, quiz_num, question_num, answer_num.

Somehow a few members got their answers submitted twice. So I need to remove the duplicated records, but make sure that one row is left behind.

There is no primary column so there could be two or three rows all with the exact same data.

Is there a query to remove all the duplicates?

Answer 1

Add Unique Index on your table:

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

Another way to do this would be:

Add primary key in your table then you can easily remove duplicates from your table using the following 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
            );

Answer 2

Thanks to jveirasv for the answer above.

If you need to remove duplicates of a specific sets of column, you can use this (if you have a timestamp in the table that vary for example)

CREATE TABLE TableA_Verify AS SELECT * FROM TableA WHERE 1 GROUP BY [COLUMN TO remove duplicates BY];

DELETE FROM TableA;

INSERT INTO TableA SELECT * FROM TAbleA_Verify;

DROP TABLE TableA_Verify;

Answer 3

This doesn't use TEMP Tables, but real tables instead. If the problem is just about temp tables and not about table creation or dropping tables, this will work:

SELECT DISTINCT * INTO TableA_Verify FROM TableA;

DROP TABLE TableA;

RENAME TABLE TableA_Verify TO TableA;

Answer 4

Add Unique Index on your table:

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

is work very well

Answer 5

An alternative way would be to create a new temporary table with same structure.

CREATE TABLE temp_table AS SELECT * FROM original_table LIMIT 0

Then create the primary key in the table.

ALTER TABLE temp_table ADD PRIMARY KEY (primary-key-field)

Finally copy all records from the original table while ignoring the duplicate records.

INSERT IGNORE INTO temp_table AS SELECT * FROM original_table

Now you can delete the original table and rename the new table.

DROP TABLE original_table
RENAME TABLE temp_table TO original_table

Answer 6

If you are not using any primary key, then execute following queries at one single stroke. By replacing values:

# table_name - Your Table Name
# column_name_of_duplicates - Name of column where duplicate entries are found

create table table_name_temp like table_name;
insert into table_name_temp select distinct(column_name_of_duplicates),value,type from table_name group by column_name_of_duplicates;
delete from table_name;
insert into table_name select * from table_name_temp;
drop table table_name_temp

1. create temporary table and store distinct(non duplicate) values
2. make empty original table
3. insert values to original table from temp table
4. delete temp table

It is always advisable to take backup of database before you play with it.