How to delete duplicate rows from a MySQL table

by 
DELETE DupRows.*
FROM MyTable AS DupRows
   INNER JOIN (
      SELECT MIN(ID) AS minId, col1, col2
      FROM MyTable
      GROUP BY col1, col2
      HAVING COUNT(*) > 1
   ) AS SaveRows ON SaveRows.col1 = DupRows.col1 AND SaveRows.col2 = DupRows.col2
      AND SaveRows.minId <> DupRows.ID;

Of course you have to extend col1, col2 in all three places to all columns.

Edit: I just pulled this out of a script I keep and re-tested, it executes in MySQL.

More Related Contents:

  1. Remove duplicate rows in MySQL
  2. Delete all Duplicate Rows except for One in MySQL? [duplicate]
  3. Find duplicate records in MySQL
  4. How to delete duplicates on a MySQL table?
  5. How to abort INSERT operation in MySql trigger?
  6. Prevent auto increment on MySQL duplicate insert
  7. Deleting duplicate rows from a table
  8. In MySQL, can I copy one row to insert into the same table?
  9. Removing duplicates with unique index
  10. How do I delete all the duplicate records in a MySQL table without temp tables
  11. MySQL delete duplicate records but keep latest
  12. How to remove duplicate entries from a mysql db?
  13. Count duplicates records in Mysql table?
  14. How to do unique constraint works with NULL value in MySQL
  15. Force MySQL to return duplicates from WHERE IN clause without using JOIN/UNION?
  16. Delete the entire content of all table in a Database [closed]
  17. MyISAM versus InnoDB [closed]
  18. How do I find the MySQL my.cnf location
  19. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  20. How to use mysql JOIN without ON condition?
  21. How to remove all non-alpha numeric characters from a string in MySQL?
  22. Define a variable within select and use it within the same select
  23. ERROR: Error 1005: Can’t create table (errno: 121)
  24. Merge two tables in one SQL query and make the date values unique
  25. mySQL subquery limit
  26. MySQL Select ID’s which occur on different rows with multiple specific values for a column
  27. How to sort a MYSQL table in a permanent way?
  28. MySQL – How to parse a string value to DATETIME format inside an INSERT statement?
  29. delete rows from multiple tables
  30. MySQL Multiple Joins in one query?

Leave a Comment