mysql 删除多余重复记录

最新推荐文章于 2024-05-16 16:00:23 发布

最新推荐文章于 2024-05-16 16:00:23 发布 · 197 阅读

文章标签：

#MySQL

java 专栏收录该内容

30 篇文章

订阅专栏

本文介绍了一种在MySQL中解决自表删除重复记录的方法。由于MySQL不允许在同一语句中先选择后更新同一表，文章提供了一个创建临时表来绕过此限制的例子。

（stu表中的名字(name)是重复的）

问题：
在Mysql下执行：

delete from stu where name in (select name from stu group by name having count(name) > 1) and id not in (select id from stu group by name having count(name) > 1);

MySql报错： You can't specify target table 'table name' for update in FROM clause

在 Mysql下执行：

select id from stu where name in (select name from stu group by name having count(name) > 1) and id not in (select id from stu group by name having count(name) > 1);

是成功的。

注意：上面的两句话很矛盾啊。

就现在的mysql版本（我用5.1）来说，不能先select出同一表中的某些值，再update这个表(在同一语句中)

解决：
create table tmp_table as select id from stu where name in (select name from stu group by name having count(name) > 1) and id not in (select id from stu group by name having count(name) > 1);
delete from stu where id in (select id from tmp_table);
drop table tmp_table;