使用 MySQL 从表中删除重复数据

Question

我正在尝试从数据库中删除重复的数据。我发现这里有一个很好的示例，说明如何在 Oracle 数据库上执行此操作。

该答案中的底部查询（仅选择重复的行）在 MySQL 中有效，但删除查询（见下文）却不起作用...

"DELETE FROM studios as a
 WHERE a.id >
       ANY (SELECT b.id
              FROM studios as b
             WHERE a.name = b.name
               AND a.email  = b.email
            )"

我得到的错误是：

You have an error in your SQL syntax; check the manual that corresponds to your 
MySQL server version for the right syntax to use near 'a
 WHERE a.id >
       ANY (SELECT b.id
              FROM studios as b
           ' at line 1

所以，我寻找了正确的 删除语法 和 任何语法使用，但不能'没有发现我的查询有任何问题...有什么想法吗？

Answer 1

尝试这个查询 -

DELETE t1 FROM studios t1
  JOIN (SELECT MIN(id) id, name, email FROM studios GROUP BY name, email) t2
    ON t1.id <> t2.id AND t1.name = t2.name AND t1.email = t2.email;

Answer 2

AS 运算符不适用于 MySQL 中的 DELETE 语句。

试试这个（未验证）：

DELETE FROM a using studios a
WHERE a.id >
ANY 
(
    SELECT b.id
    FROM studios as b
    WHERE a.name = b.name
    AND a.email  = b.email
)