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);
依然可以执行。