前提条件: 表 cor_users , 关键字段 id username
现在的情况是: 表中已有几千条数据,由于一开始没注意username 不能重复,导致表中有好多重名(username)数据;
需求: 需要剔除重名的数据,重名数据需要保留一条(保留id最小的)
一般写法:
delete from cor_users where username in (
select username from cor_users group by username having count(username) >1
) and id not in(
select min(id) id from cor_users group by username having count(username) >1
);
出现报错, 1093-You can’t specify target table for.......
改善后写法:
delete from cor_users where username in (
select username from (
select username from cor_users group by username having count(username) >1
) a
) and id not in(
select id from (
select min(id) id from cor_users group by username having count(username ) >1
) b
);
绿色部分为增加的代码,增加后可以正常执行!!!!