MySQL 5.7 错误 (1093 : You can't specify target table ___ for update in FROM clause) - usual solution not working

Question

我有一个表“employees”，我试图将一些属性(例如薪水)设置为与表中其他值相同的值。我对这个错误的理解是，可以通过使用临时表的以下解决方法来避免它:

UPDATE employees
SET salary=(SELECT salary FROM (SELECT * FROM employees WHERE employee_id= '123') AS t1)
WHERE employee_id='456';

但是，当我尝试此操作时，仍然收到相同的错误代码(“无法在 FROM 子句中指定要更新的目标表‘employees’”)。这里还有其他问题吗？

Answer 1

问题是 functional change在 mysql 5.7 中，滚动到最后

The optimizer now handles derived tables and views in the FROM clause in consistent fashion to better avoid unnecessary materialization and to enable use of pushed-down conditions that produce more efficient execution plans. However, for statements such as DELETE or UPDATE that modify tables, using the merge strategy for a derived table that previously was materialized can result in an ER_UPDATE_TABLE_USED error:

使用 JOIN 或强制 otimizer 的行为与以前的版本一样:

SET optimizer_switch = 'derived_merge=off';