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

by

Editor warning: This solution is computationally inefficient and may bring down your connection for a large table.

NB – You need to do this first on a test copy of your table!

When I did it, I found that unless I also included AND n1.id <> n2.id, it deleted every row in the table.

  1. If you want to keep the row with the lowest id value: 

    DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name

  2. If you want to keep the row with the highest id value:

    DELETE n1 FROM names n1, names n2 WHERE n1.id < n2.id AND n1.name = n2.name

I used this method in MySQL 5.1

Not sure about other versions.

Update: Since people Googling for removing duplicates end up here
Although the OP’s question is about DELETE, please be advised that using INSERT and DISTINCT is much faster. For a database with 8 million rows, the below query took 13 minutes, while using DELETE, it took more than 2 hours and yet didn’t complete. 

INSERT INTO tempTableName(cellId,attributeId,entityRowId,value)
    SELECT DISTINCT cellId,attributeId,entityRowId,value
    FROM tableName;

More Related Contents:

  1. Remove duplicate rows in MySQL
  2. Deleting duplicate rows from a table
  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. How to find similar results and sort by similarity?
  7. Unknown column in ‘field list’ error on MySQL Update query
  8. Insert into multiple tables in one query
  9. generate_series() equivalent in MySQL
  10. MySQL : isn’t in GROUP BY
  11. Why does MySQL report a syntax error on FULL OUTER JOIN?
  12. Error creating table: You have an error in your SQL syntax near ‘order( order_id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id ‘ at line 1 [duplicate]
  13. Mixing ANSI 1992 JOINs and COMMAs in a query
  14. SQL: Creating a Relational table with 2 different auto_increment
  15. How do I add a custom CHECK constraint on a MySQL table?
  16. SQL Query to get column values that correspond with MAX value of another column?
  17. MySQL : transaction within a stored procedure
  18. Select from one table where not in another
  19. How to select the most recent set of dated records from a mysql table
  20. Second SELECT query if first SELECT returns 0 rows
  21. Set value to NULL in MySQL
  22. SELECT query return 1 row from each group
  23. MySql Single Table, Select last 7 days and include empty rows
  24. mysql SQL: specific item to be first and then to sort the rest of the items
  25. When is a timestamp (auto) updated?
  26. MySQL Group By Hours
  27. RMySQL dbWriteTable with field.types
  28. Force MySQL to return duplicates from WHERE IN clause without using JOIN/UNION?
  29. Convert SQL Server query to MySQL [duplicate]
  30. comparison of truncate vs delete in mysql/sqlserver [duplicate]

Leave a Comment