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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt  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  lt  n2 id AND n1 member id   n2 member id and n1 answer num  n2 answer num

User · Answer

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

User · Answer

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     gt  1                      AS A

User · Answer

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    create temporary table and store distinct non duplicate  values  make empty original table insert values to original table from temp table delete temp table   It is always advisable to take backup of database before you play with it

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

Examples related to mysql

Examples related to sql

Examples related to duplicates

Examples related to sql-delete

Examples related to unique-index