mysql 重复数据删除
业务中遇到要从表里删除重复数据的需求。想当然写了下面的语句。
//示例表: CREATE TABLE `users` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` char(50) NOT NULL, PRIMARY KEY (`id`) )
delete from users where id in (select min(id) from users group by name having count(name)>1);
结果报错:1093 you can't specify target table ....
原因是mysql删除动作不能带有本表的查询动作,意思是你删除users表的东西不能以users表的信息为条件 所以这个语句会报错,执行不了。只要通过创建临时表作为查询条件。如下
delete from users where id in (select * from (select min(id) from users group by name having count(name)>1));
还要注意 delete from users 这里不能用别名
其他方法。
建立零时表:
create table tmp_users