1093错误:mysql不能指定更新的目标表在FROM子句

mysql> delete from t where id in (select id from t where id < 5); ERROR 1093 (HY000): You can’t specify target table ‘t’ for update in FROM clause mysql>

这样删除将报错,更改SQL语句为

mysql> delete from t where id in (select * from (select id from t where id < 5) tmp); Query OK, 4 rows affected (0.00 sec)

以这样的形式即可删除。

 

update wp_postmeta set meta_value = 9999 where meta_id = (select meta_id from wp_postmeta where post_id = 6068 and meta_key = ‘views’);

这样删除将报错,更改SQL语句为

update wp_postmeta set meta_value = 9999 where meta_id =(select * from (select meta_id from wp_postmeta where post_id = 6068 and meta_key = ‘views’) tmp);

以这样的形式即可删除。

 

分析:

先把要删除的目标放到一张临时表再把要删除的条件指定到这张临时表即可。

结论:

也就是说将select出的结果再通过中间表select一遍,这样就规避了错误。注意,这个问题只出现于mysql,mssql和oracle不会出现此问题。