Deleting duplicate rows from a table

by

Yes, assuming you have a unique ID field, you can delete all records that are the same except for the ID, but don’t have “the minimum ID” for their group of values.

Example query:

DELETE FROM Table
WHERE ID NOT IN
(
SELECT MIN(ID)
FROM Table
GROUP BY Field1, Field2, Field3, ...
)

Notes:

  • I freely chose “Table” and “ID” as representative names
  • The list of fields (“Field1, Field2, …”) should include all fields except for the ID
  • This may be a slow query depending on the number of fields and rows, however I expect it would be okay compared to alternatives

EDIT: In case you don’t have a unique index, my recommendation is to simply add an auto-incremental unique index. Mainly because it’s good design, but also because it will allow you to run the query above.

More Related Contents:

  1. Remove duplicate rows in MySQL
  2. Delete all Duplicate Rows except for One in MySQL? [duplicate]
  3. How do I delete all the duplicate records in a MySQL table without temp tables
  4. If two columns are 1 then make new column
  5. How can I return pivot table output in MySQL?
  6. Using LIMIT within GROUP BY to get N results per group?
  7. How to abort INSERT operation in MySql trigger?
  8. Retrieving the last record in each group – MySQL
  9. Required to join 2 tables with their FKs in a 3rd table
  10. Does COUNT(*) always return a result?
  11. Ordering by specific field value first
  12. How to change the CHARACTER SET (and COLLATION) throughout a database?
  13. Connect By Prior Equivalent for MySQL
  14. Mysql::Error: Specified key was too long; max key length is 1000 bytes
  15. How to convert all tables from MyISAM into InnoDB?
  16. How can I get around MySQL Errcode 13 with SELECT INTO OUTFILE?
  17. Are there disadvantages to using a generic varchar(255) for all text-based fields?
  18. SQL Query to show nearest date?
  19. How to create relationships in MySQL
  20. MySQL Select last 7 days
  21. How to select the most recent set of dated records from a mysql table
  22. Second SELECT query if first SELECT returns 0 rows
  23. Set value to NULL in MySQL
  24. Difference between two table structure
  25. Difference between SET autocommit=1 and START TRANSACTION in mysql (Have I missed something?)
  26. How to retrieve JSON data from MySQL?
  27. MySQL equivalent of Oracle’s SEQUENCE.NEXTVAL
  28. When to use a left outer join?
  29. Optimistic locking in MySQL
  30. Insert and set value with max()+1 problems

Leave a Comment