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
SETretail = retail * 0.9
WHEREid IN (SELECTidFROMitemsWHEREretail / wholesale >= 1.3AND quantity > 100);

这个语句执行的时候会报错： ERROR 1093 (HY000): You can't specify target table 'items' for update in FROM clause

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

方法一 多表更新

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

update items,
(SELECTidFROMitemsWHEREretail / wholesale >= 1.3AND quantity > 100
) a
setretail = items.retail * 0.9
whereitems.id = a.id

方法二 中间表

中间表的含义是将子查询再包一层，比如上面的语句可以改写成：

UPDATE items
SETretail = retail * 0.9
WHEREid IN (selectidfrom(SELECTidFROMitemsWHEREretail / wholesale >= 1.3AND 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 在看” 你最好看！

                                                                喜欢，就关注我吧！