mysql - 查询删除重复项生成错误

我正在尝试根据客户电话号码从表中删除重复项。下面的内部选择查询正确选择了我需要删除的所有重复记录，但是外部删除查询会产生以下错误：

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

查询：

DELETE FROM customers WHERE id IN (SELECT id from customers GROUP BY phone HAVING COUNT(phone) > 1)

最佳答案

在update和delete语句中，通常不能引用要修改的表。一个简单的解决方案是使用join：

DELETE c
    FROM customers c JOIN
         (SELECT phone, COUNT(*) as cnt
          FROM customers c
          GROUP BY phone
         ) p
         ON c.phone = p.phone AND cnt > 1;

请注意，这将从表中删除所有重复项。通常，您要保留其中之一：

DELETE c
    FROM customers c LEFT JOIN
         (SELECT phone, MIN(id) as minid
          FROM customers c
          GROUP BY phone
         ) p
         ON c.id = p.minid
    WHERE p.phone IS NULL;

关于mysql - 查询删除重复项生成错误，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/33595785/