查找重复的，并且除掉最小的那个。
delete users_groups as a from users_groups as a,
(
select *,min(id) from users_groups group by uid having count(1) > 1
) as b
where a.uid = b.uid and a.id > b.id;
(7 row(s)affected)
(0 ms taken)
query result(7 records)
id uid gid
1 11 502
2 107 502
3 100 503
4 110 501
5 112 501
6 104 502
9 102 501
———————-
这样形式的语句
delete from blur_article where id not in(select min(id) from blur_article group by title)
在ms sql server里可以运行，但mysql并不支持，而会报错：
#1093 – You can’t specify target table ‘xxx’ for update in FROM clause
也就是说，不能先select出同一表中的某些值，再update这个表(在同一语句中)
替换方案：
create table tmp as select min(id) as col1 from blur_article group by title;
delete from blur_article where id not in (select col1 from tmp);
drop table tmp;
————————–
重点在 SELECT a.cabf_id FROM (select tmp.* from wms_cabinet_form tmp) a ，我 select tmp.* from wms_cabinet_form tmp 作为子集，
然后再 select a.cabf_id FROM 子集，这样就不会 select 和 update 都是同一个表。致此问题得到完美解决。
————————-
原句:delete from menu_item where parent_id =(select menu_item_id from menu_item where menu_data like ‘%manageVendors%’);
修改后:delete from menu_item where parent_id =(select temp.menu_item_id from (select m.menu_item_id,m.menu_data from menu_item m) temp where temp.menu_data like ‘%manageVendors%’);
0 条评论。