如何从mysql中的表中删除重复的行 | 如何从mysql中的表中删除重复的行

本文介绍了如何从mysql中的表中删除重复的行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我需要从mysql中的表中删除重复的记录.因此，我有一个表名"employee"，字段为empid，empname，empssn

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

但是我有一个mysql错误是

but i have a mysql error is

推荐答案

在这种情况下，将其包装到派生表中的技巧是否可行? (基于 http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/)

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
);

编辑是的，这似乎可行.

这篇关于如何从mysql中的表中删除重复的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！