查找重复的,并且除掉最小的那个。
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%’);
延伸阅读
- DB2MYSQL:最好用的ms access转换为mysql工具/开源免费/access 导入mysql
- MySQL之Covering Index
- MYSQl联合查询效率分析:left join
- MySQL数据类型:TINYINT,SMALLINT,MEDIUMINT,INT,INTEGER,BIGINT;text,longtext,mediumtext,ENUM,SET等字段类型区别
- MySQL复制设置及相关维护操作
- MySQL索引详解,及MySQL高效运行优化
- 一次linux下mysql服务优化(环境为vps上的centos5)
- MySQL数据库查询缓存Query Cache问题详解
- mysql 索引管理:SHOW INDEX语法 查看索引状态
- 实例详解MySQL配置参数 my.ini/my.cnf
- MySQL查询及删除重复记录的方法
- MYSQL中删除重复记录的方法
- mysql中删除重复记录,只保留一条
0 条评论。