mysql - mysql删除表中重复行的方法

Question

我需要从 mysql 表中删除重复记录。所以我有一个表名“employee”字段是 empid、empname、empssn

为了获取重复记录我写了一个查询

SELECT COUNT(empssn), empssn FROM employee 
GROUP BY empssn 
HAVING COUNT(empssn) > 1

现在我想删除重复的记录。为此，我写了查询是。

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

你可以假设表中的记录是

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

但是我有一个mysql错误是

You can't specify target table 'employee' for update in FROM clause

Answer 1

将其包装到派生表中的技巧是否适用于这种情况？ (基于 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
);

编辑 是的，它似乎为此工作。