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

by

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

More Related Contents:

  1. Remove duplicate rows in MySQL
  2. Delete all Duplicate Rows except for One in MySQL? [duplicate]
  3. How to delete from multiple tables in MySQL?
  4. MySQL DELETE FROM with subquery as condition
  5. SQL query: Delete all records from the table except latest N?
  6. Deleting duplicate rows from a table
  7. SQL DELETE with JOIN another table for WHERE condition
  8. Removing duplicates with unique index
  9. Deleting a row based on the max value
  10. Using LIMIT within GROUP BY to get N results per group?
  11. How to perform grouped ranking in MySQL
  12. How to abort INSERT operation in MySql trigger?
  13. Required to join 2 tables with their FKs in a 3rd table
  14. Does COUNT(*) always return a result?
  15. Ordering by specific field value first
  16. How to change the CHARACTER SET (and COLLATION) throughout a database?
  17. Mysql::Error: Specified key was too long; max key length is 1000 bytes
  18. How can I get around MySQL Errcode 13 with SELECT INTO OUTFILE?
  19. Are there disadvantages to using a generic varchar(255) for all text-based fields?
  20. SQL Query to show nearest date?
  21. Getting all parent rows in one SQL query
  22. Mysql Improve Search Performance with wildcards (%%)
  23. Replace Into Query Syntax
  24. Difference between SET autocommit=1 and START TRANSACTION in mysql (Have I missed something?)
  25. How to retrieve JSON data from MySQL?
  26. MySQL equivalent of Oracle’s SEQUENCE.NEXTVAL
  27. Delete from one table with join
  28. When to use a left outer join?
  29. MySQL OPTIMIZE all tables?
  30. Strip last two characters of a column in MySQL

Leave a Comment