MySQL 5.7错误（1093：无法指定目标表___更新在FROM子句中） - 通常的解决方案不起作用

Question

我有一个表'employees'，我试图设置一些属性（例如工资）与表中其他值相同的值。我的这个错误的理解是，它可以用以下解决方法来避免使用临时表：

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

不过，我仍然得到同样的错误代码（“不能指定目标表‘员工’的在FROM子句中更新“）。这里还有其他问题吗？

Answer 1

的问题是在MySQL 5.7 functional change，滚动接近尾声

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';