how to delete duplicate rows from a table in mysql

by

Does the trick of wrapping it into a derived table work for this case? (Based on http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/)

DELETE FROM employee 
WHERE (empid, empssn) NOT IN (
    SELECT empid, empssn FROM (
        SELECT MIN(empid) AS empid, empssn FROM employee 
        GROUP BY empssn
    ) X
);

Edit Yep it seems to work this end.

More Related Contents:

  1. i have an error which i dont understand. can you help me? [closed]
  2. MySQL Error 1093 – Can’t specify target table for update in FROM clause
  3. MySQL Server has gone away when importing large sql file
  4. How to display UTF-8 characters in phpMyAdmin?
  5. #1071 – Specified key was too long; max key length is 1000 bytes
  6. Restore table structure from frm and ibd files
  7. Why is the estimated rows count very different in phpmyadmin results?
  8. Subqueries with EXISTS vs IN – MySQL
  9. phpMyAdmin on MySQL 8.0 [duplicate]
  10. Find and Replace text in the entire table using a MySQL query
  11. Setting up foreign keys in phpMyAdmin?
  12. Is there a simple way to convert MySQL data into Title Case?
  13. SQL DELETE with JOIN another table for WHERE condition
  14. MySQL #1093 – You can’t specify target table ‘giveaways’ for update in FROM clause
  15. Error Dropping Database (Can’t rmdir ‘.test\’, errno: 17)
  16. MySQL said: Documentation #1045 – Access denied for user ‘root’@’localhost’ (using password: NO)
  17. How to Import Multiple csv files into a MySQL Database
  18. Composite key as foreign key (sql)
  19. Can’t import database through phpmyadmin file size too large
  20. #1045 – Access denied for user ‘root’@’localhost’ (using password: YES)
  21. Access denied after setting user’s password with SHA256 in phpMyAdmin
  22. where does MySQL store database files?
  23. #1130 – Host ‘localhost’ is not allowed to connect to this MySQL server
  24. How to import large sql file in phpmyadmin
  25. SQL UPDATE with sub-query that references the same table in MySQL
  26. Import CSV to Update rows in table
  27. How to get back Lost phpMyAdmin Password, XAMPP
  28. UTF8 problem with MySQL 5
  29. mySQL query to search all tables within a database for a string?
  30. Insert and set value with max()+1 problems

Leave a Comment