mysql 删除多余重复记录
(stu表中的名字(name)是重复的)
问题:
在Mysql下执行:
select id from stu where name in (select name from stu groupby name having count(name) > 1) and id not in (select id from stu group byname 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 groupby name having count(name) > 1) and id not in (select id from stu group byname having count(name) > 1);
delete from stu where id in (select id from tmp_table);
drop table tmp_table;