how to delete duplicate rows from a table in mysql

Question

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

Mr.Singh · Accepted Answer · 2022-05-06 18:27:23Z

10

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.

edited May 6, 2022 at 18:27

Mr.Singh

2,0457 gold badges32 silver badges64 bronze badges

answered Jul 17, 2010 at 12:08

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to delete duplicate rows from a table in mysql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related