How do I delete all the duplicate records in a MySQL table without temp tables

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?

Fideicommissary

Is there a reason you are against creating a temporary table that could be removed with a simple drop table statement after the duplicates have been removed? I could provide you with SQL to create a temp table with only unique records, delete records from original table, load unique data then drop the temp table. If it isn't a massive database this shouldn't take too long. Here's a good article on the process: databasejournal.com/features/mysql/article.php/10897_2201621_2/… – Equalizer

possible duplicate of How to delete duplicate records in mysql database? – Sisson

I am looking for a solution that is easily (in the broadest sense of the word) repeatable on-the-fly. Using extra tables, temp or not, means bringing down the site any time this is detected. The best solution would be to make sure it never happens in the first place, but until then, I want this check/fix to be done on a routine basis to make sure the reports don't give wonky results – Fideicommissary

As I have mentioned in my answer create an index on your table It will remove duplicate data and also not allow you to add duplicate data in future – Clerestory

Possible duplicate of Remove duplicate rows in MySQL – Abundance

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
            );

Clerestory

I'll go ahead and choose this as the answer. In the end I'm going to have to use a temp table to clean house, but I'll make sure to add the UNIQUE INDEX so there won't be this issue in the future. Now I just need to figure out how things got duplicated in the first place. – Fideicommissary

Note: If your table contains 3 or more duplicates of the same row, you will need to run this more than once – Mccurdy

This answer would be more helpful if there was an explanation of the consequences of adding a unique index with IGNORE: apparently it deletes duplicate rows? Do we know if it keeps the first one? – Yes

It looks like the alter ignore table might go away soon: dev.mysql.com/worklog/task/?id=7395 – Eskilstuna

I think delete command will delete all the duplicate data. It will not contain a single row of duplicate data. – Krystynakshatriya

Note that alter ignore table worked for me only when set session old_alter_table=1 was used before it. – Scathe

@Eskilstuna was correct: "As of MySQL 5.7.4, the IGNORE clause for ALTER TABLE is removed and its use produces an error." – Ankara

Answer 2

Instead of drop table TableA, you could delete all registers (delete from TableA;) and then populate original table with registers coming from TableA_Verify (insert into TAbleA select * from TAbleA_Verify). In this way you won't lost all references to original table (indexes,... )

CREATE TABLE TableA_Verify AS SELECT DISTINCT * FROM TableA;

DELETE FROM TableA;

INSERT INTO TableA SELECT * FROM TAbleA_Verify;

DROP TABLE TableA_Verify;

Verjuice

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;

Breuer

Answer 4

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;

Iveson

Answer 5

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

Pettis

thank you, it work, but i got 1 warning(s): 1681 'IGNORE' is deprecated and will be removed in a future release. Duplicates: 1 Warnings: 1 – Trimester

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.

Terryn

Answer 7

As noted in the comments, the query in Saharsh Shah's answer must be run multiple times if items are duplicated more than once.

Here's a solution that doesn't delete any data, and keeps the data in the original table the entire time, allowing for duplicates to be deleted while keeping the table 'live':

alter table tableA add column duplicate tinyint(1) not null default '0';

update tableA set
duplicate=if(@member_id=member_id
             and @quiz_num=quiz_num
             and @question_num=question_num
             and @answer_num=answer_num,1,0),
member_id=(@member_id:=member_id),
quiz_num=(@quiz_num:=quiz_num),
question_num=(@question_num:=question_num),
answer_num=(@answer_num:=answer_num)
order by member_id, quiz_num, question_num, answer_num;

delete from tableA where duplicate=1;

alter table tableA drop column duplicate;

This basically checks to see if the current row is the same as the last row, and if it is, marks it as duplicate (the order statement ensures that duplicates will show up next to each other). Then you delete the duplicate records. I remove the duplicate column at the end to bring it back to its original state.

It looks like alter table ignore also might go away soon: http://dev.mysql.com/worklog/task/?id=7395

Eskilstuna

Answer 8

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

Fortunio

Answer 9

Tested in mysql 5.Dont know about other versions. If you want to keep the row with the lowest id value:

DELETE n1 FROM 'yourTableName' n1, 'yourTableName' n2 WHERE n1.id > n2.id AND n1.member_id = n2.member_id and n1.answer_num =n2.answer_num

If you want to keep the row with the highest id value:

DELETE n1 FROM 'yourTableName' n1, 'yourTableName' n2 WHERE n1.id < n2.id AND n1.member_id = n2.member_id and n1.answer_num =n2.answer_num

Agriculture