mysql5.7 1093,MySQL 5.7错误（1093：您无法在FROM子句中指定目标表___进行更新）-常规解决方案不起作用...

I have a table 'employees' and I'm trying to set some attributes (e.g. salary) to the same value as some other value in the table. My understanding of this error is that it can be avoided with the following workaround, using a temporary table:

UPDATE employees

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

WHERE employee_id='456';

However, I am still getting the same error code ("can't specify target table 'employees' for update in FROM clause") when I try this. Is there some other issue here?

解决方案

The issue is a functional change in mysql 5.7,scroll towards the end

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:

Either use the JOIN or force the otimizer to behave like in the previous version with:

SET optimizer_switch = 'derived_merge=off';