点滴记忆*记忆点滴

业务中遇到要从表里删除重复数据的需求。想当然写了下面的语句。

//示例表：
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  select min(`id`), `name`   from users group by name ;