JAVA高级面试进阶训练营视频教程

admin

update语句中包含的子查询的表和update的表为同一张表时，报错：1093-You can’t specify target table for update in FROM clause

mysql不允许update目标表和子查询里面的表为同一张表

错误sql：
UPDATE mg_brand set `status`='0' where iID=(SELECT id from mg_industry where `name`='汽车') and id in (SELECT id from mg_brand WHERE nameC = '欧宝' or pID = (SELECT id from mg_brand WHERE nameC = '欧宝'));

解决办法：子查询sql可以改变双层的子查询，即可执行成功
示例sql:
UPDATE mg_brand SET `status` = '0' WHERE iID = ( SELECT id FROM mg_industry WHERE `name` = '汽车' ) AND id IN ( SELECT id FROM (SELECT id FROM mg_brand) AS temp WHERE nameC = '欧宝' OR pID = ( SELECT id FROM ( SELECT id FROM mg_brand WHERE nameC = '欧宝' ) AS te WHERE 1 ));