手机浏览 RSS 2.0 订阅 膘叔的简单人生 , 腾讯云RDS购买 | 超便宜的Vultr , 注册 | 登陆
浏览模式: 标准 | 列表Tag:子查询

update sql

Submitted by gouki on 2011, October 23, 2:05 PM

更新数据库的时候，提示： You can't specify target table 'channels' for update in FROM clause
出错的SQL是什么呢？
出错的是SQL是：
update channels set parentid = 0 where parentid in (select b.id from channels b where b.parentid = 0) ;
从理论上来看好象没什么错，但就是出现上面的错误（You can't specify target table 'channels' for update in FROM clause），说是不能在原表操作。于是改正它。。
改成：
update channels set parentid = 0 where parentid IN ( select id from (SELECT id from channels where parentid = 0) as tmp )
看上去是不是很恶心？但确实是通过临时表来解决了这个问题。。。
好吧，我又恶心了

Tags: update, 子查询

DataBase | 评论:0 | 阅读:16147

mysql子查询删除

Submitted by gouki on 2011, October 15, 6:06 PM

这是我试了很多的代码之后才。。。。
#select id,count(1) as cnt from feeds_datas group by link HAVING count(1) > 7 order by cnt DESC
#delete from feeds_datas where link = (select link from feeds_datas WHERE id in ()
#delete from feeds_datas where id in (6697,7127,6798,4557,4558,6086,6087,6088,6089)
#select id from feeds_datas group by link HAVING count(link) > 1

#delete from feeds_datas as a WHERE a.in in (select b.id from feeds_datas as b group by b.link HAVING count(b.link) > 1 )
#delete from feeds_datas t1,(select link from feeds_datas group by link HAVING count(1) > 1) t2 where t1.link = t2.link

#and id not in (select min(id) from  feeds_datas  group by link  having count(link )>1)

delete feeds_datas as a from feeds_datas as a,
(
select *,min(id) from feeds_datas group by link having count(1) > 1
) as b
 where a.link = b.link and a.id > b.id;

加#的都是失败的，其中最后一条SQL我很纳闷，如果把delete 后面的 feeds_datas as a去掉，那么sql就报错，说是不许right syntax之类的。加上就OK。
记录一下。。
网上有很多资料，可以参考：
1、http://yueliangdao0608.blog.51cto.com/397025/81390
2、http://zhidao.baidu.com/question/85817899

Tags: mysql, 子查询, 删除

DataBase | 评论:1 | 阅读:19810