【问题处理】—— Mysql : You can‘t specify target table for update in FROM clause

【问题处理】—— Mysql : You can‘t specify target table for update in FROM clause

项目场景:

Mysql环境,业务操作报错


问题描述

错误提示:You can‘t specify target table for update in FROM clause

原因分析:

根据Mysql官方文档的描述:
You cannot update a table and select directly from the same table in a subquery. You can work around this by using a multi-table update in which one of the tables is derived from the table that you actually wish to update, and referring to the derived table using an alias:

即:不能在子查询中更新表并直接从同一表中进行选择。您可以通过使用多表更新来解决此问题,其中一个表是从您实际希望更新的表派生的,并使用别名引用派生表

说人话就是当你更新某一张表A时,where后面的条件就不能直接包含(select XXX from A)这样的子句,至于原因官方则没说


解决方案:

官方示例方案:

错误用法:
UPDATE items
      SET retail = retail * 0.9
      WHERE id IN
          (SELECT id FROM items
              WHERE retail / wholesale >= 1.3 AND quantity > 100);
ERROR 1093 (HY000): You can't specify target table 'items' for update in FROM clause
正确用法:可以使用多表更新,将子查询移动到要update的表后,然后再WHERE子句中使用别名引用它构造连接
UPDATE items,
       (SELECT id FROM items
        WHERE id IN
            (SELECT id FROM items
             WHERE retail / wholesale >= 1.3 AND quantity < 100))
        AS discounted
SET items.retail = items.retail * 0.9
WHERE items.id = discounted.id;

个人方案:没有什么是套一层不能解决的,如果有,那就套两层:

UPDATE items
      SET retail = retail * 0.9
      WHERE id IN
          (select id from (SELECT id FROM items
              WHERE retail / wholesale >= 1.3 AND quantity > 100) s);
转载请说明出处内容投诉
CSS教程_站长资源网 » 【问题处理】—— Mysql : You can‘t specify target table for update in FROM clause

发表评论

欢迎 访客 发表评论

一个令你着迷的主题!

查看演示 官网购买