how to delete duplicate rows from a table in mysql

mysql, mysql-error-1093, phpmyadmin

Solution

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.

Problem

I need to delete duplicate record from table in mysql. So i have a table name "employee" fields are empid, empname, empssn for getting duplicate record i have written a query ``` SELECT COUNT(empssn), empssn FROM employee GROUP BY empssn HAVING COUNT(empssn) > 1 ``` Now I want to delete duplicate records. For that I have written query is. ``` DELETE FROM employee WHERE (empid, empssn) NOT IN ( SELECT MIN(empid), empssn FROM employee GROUP BY empssn ); ``` you can assume records in table are ``` EmpId EmpName EmpSSN ------------------------------- 1 Jack 555-55-5555 2 Joe 555-56-5555 3 Fred 555-57-5555 4 Mike 555-58-5555 5 Cathy 555-59-5555 6 Lisa 555-70-5555 7 Jack 555-55-5555 8 Mike 555-58-5555 9 Cathy 555-59-5555 10 Lisa 555-70-5555 11 Lisa 555-70-5555 ``` but I have a mysql error is You can't specify target table 'employee' for update in FROM clause

Original source