By MivaScott


2012-12-26 21:15:45 8 Comments

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?

8 comments

@nikolais 2014-04-01 12:58:11

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;

@jveirasv 2013-10-22 17:12:12

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;

@Saharsh Shah 2012-12-27 06:49:13

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

@MivaScott 2013-01-16 16:47:00

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.

@Karma 2015-01-07 04:12:31

Brilliant Stuff!!!! Thanks a lot! :)

@Saharsh Shah 2015-01-07 05:54:24

@KarmicDice You're most welcome...

@zgr024 2015-04-27 13:07:56

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

@LarsH 2015-04-29 16:28:47

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?

@juacala 2015-09-09 16:29:34

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

@openwonk 2015-11-21 23:31:47

alter ignore table worked great!!!!

@Ankit Bajpai 2015-12-11 15:17:20

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

@Andrea php 2016-07-05 18:01:30

Thank you, work perfect!

@Andron 2018-04-24 13:57:55

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

@Illya Moskvin 2018-12-27 22:42:00

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

@user1838915 2016-01-21 05:41:51

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

@Sandesh Mhatre 2015-02-24 13:06:18

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.

@juacala 2015-09-08 21:01:07

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

@Dina Elwy 2014-04-02 08:15:36

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

@Lebnik 2015-09-04 15:27:15

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

@christoph 2012-12-26 22:53:41

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;

Related Questions

Sponsored Content

10 Answered Questions

[SOLVED] Duplicating a MySQL table, indices, and data

  • 2010-07-19 09:53:57
  • xkcd150
  • 421756 View
  • 598 Score
  • 10 Answer
  • Tags:   mysql

42 Answered Questions

[SOLVED] How to import an SQL file using the command line in MySQL?

25 Answered Questions

[SOLVED] Retrieving the last record in each group - MySQL

27 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 2426765 View
  • 1650 Score
  • 27 Answer
  • Tags:   sql duplicates

22 Answered Questions

[SOLVED] How to reset AUTO_INCREMENT in MySQL?

20 Answered Questions

[SOLVED] Get list of all tables in Oracle?

  • 2008-10-15 17:54:49
  • vitule
  • 2053331 View
  • 1033 Score
  • 20 Answer
  • Tags:   sql oracle

15 Answered Questions

[SOLVED] How to Delete using INNER JOIN with SQL Server?

37 Answered Questions

[SOLVED] How can I remove duplicate rows?

2 Answered Questions

[SOLVED] Delete all Duplicate Rows except for One in MySQL?

  • 2011-01-13 20:58:38
  • Highway of Life
  • 424966 View
  • 395 Score
  • 2 Answer
  • Tags:   mysql sql duplicates

22 Answered Questions

[SOLVED] Find duplicate records in MySQL

  • 2009-05-12 18:24:21
  • Chris Bartow
  • 719997 View
  • 613 Score
  • 22 Answer
  • Tags:   mysql duplicates

Sponsored Content