mysql中You can’t specify target table for update in FROM clause错误

mysql中You can’t specify target table for update in FROM clause错误

You cannot update a table and select directly from the same table in a subquery.

mysql官网中有这句话，我们不能在一个语句中先在子查询中从某张表查出一些值，再update这张表。

比如：

mysql> 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

针对这种报错，有两种方法解决：多表更新和中间表

方法一 多表更新

我们可以通过多表更新来解决这个问题，连表时一张表是实际要更新的表，另一长表是原来的子查询派生出来的表，我们使用别名引用派生表。比如上面的例子中的sql，可以改写成如下写法：

update items,
(
  SELECT
    id
  FROM
    items
  WHERE
    retail / wholesale >= 1.3
    AND quantity > 100
) a
set
  retail = items.retail * 0.9
where
  items.id = a.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
      ) aa
  );

需要注意的是，mysql优化器可能会优化子查询，这时候仍然会抛出错误。我们可以使用optimizer_switch变量关闭这种行为;

#将derived_merge标志设置为off
SET optimizer_switch='derived_merge=off';

derived_merge是MySQL中的查询优化技术之一，用于控制派生表合并（derived merge）优化的行为。 在MySQL中，当执行复杂的查询时，可能会涉及到派生表，即在查询中使用子查询作为临时表。派生表合并是一种优化技术，它尝试将多个派生表合并为一个更简单的查询结构，以提高查询性能

参考： https://fanyi.youdao.com/index.html#/ https://dev.mysql.com/doc/refman/8.0/en/update.html

                                                        点个“赞 or 在看” 你最好看！

                                                                喜欢，就关注我吧！