mysql 删除重复记录

查找重复的,并且除掉最小的那个。
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%’);




coded by nessus
发表评论?

0 条评论。

发表评论