使用mysql进行delete from xx in(子查询) 操作时，若子查询的 FROM ...

最新推荐文章于 2024-06-07 14:40:13 发布

zzm_

最新推荐文章于 2024-06-07 14:40:13 发布

阅读量424

点赞数

文章标签：数据库

原文链接：https://my.oschina.net/skq/blog/133542

版权

为什么80%的码农都做不了架构师？>>>

mysql> delete from user_goods where rec_id in (select b.rec_id from user_goods as ug inner join goods as g on ug.goods_id=g.goods_id where b.expired_time<1351612800);

ERROR 1093 (HY000): You can’t specify target table ‘user_goods’ for update in FROM clause

针对“同一张表”这个限制，可以通过增加别名的方式就可以避免了。如：

delete from user_goods where rec_id in (select * from (select b.rec_id from user_goods as ug inner join goods as g on ug.goods_id=g.goods_id where b.expired_time<1351612800) as tmp_result);

网上有人说，子查询里面不能有where条件，LZ专门试了一下，并没有这个限制！

delete from user_goods where rec_id in (select * from (select b.rec_id from user_goods as ug inner join goods as g on ug.goods_id=g.goods_id where b.expired_time<1351612800) as tmp_result where 1);

依然可以执行。

转载：http://54chengxuyuan.com/archives/88

转载于:https://my.oschina.net/skq/blog/133542