mysql 删除重复记录

作者: 日期: 2010/12/28 发表评论 (0) 查看评论

查找重复的，并且除掉最小的那个。
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 条评论。

发表评论