使用MySQL从表中删除重复的数据 [英] Removing Duplicate Data from a Table using MySQL

问题描述

我正在尝试从数据库中删除重复的数据。我发现在这里的一个很好的例子如何在oracle数据库上这样做。

I am trying to remove duplicate data from my database. I found a nice example on here of how to do this on an oracle database.

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

The bottom query from that answer (only selecting the duplicate rows) works in MySQL, but the delete query (see below) does not...

"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

所以，我看了一下正确的删除语法和任何语法使用，但找不到任何错误我的查询...任何想法？

So, I had a look for the right delete syntax and any syntax to use, but couldn't find anything wrong with my query... Any ideas?

推荐答案

尝试这个查询 - / p>

Try this query -

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;

这篇关于使用MySQL从表中删除重复的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！