MySQL 5.7错误(1093:您无法在FROM子句中指定目标表___进行更新)-常规解决方案不起作用 [英] MySQL 5.7 error (1093: You can't specify target table ___ for update in FROM clause) - usual solution not working

问题描述

我有一个表'employees'，并且我试图将某些属性(例如薪金)设置为与表中其他值相同的值.我对这个错误的理解是，可以通过以下临时表使用临时表来避免该错误:

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

但是，尝试此操作时，我仍然收到相同的错误代码(无法在FROM子句中指定目标表'员工'进行更新").这里还有其他问题吗?

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?

推荐答案

问题是

优化器现在可以在FROM子句中处理派生表和视图 以一致的方式更好地避免不必要的实现 使能够使用下推条件产生更高效率 执行计划.但是，对于诸如DELETE或UPDATE这样的语句， 使用合并策略对派生表进行修改表 以前的实现可能会导致ER_UPDATE_TABLE_USED 错误:

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或通过以下方式强制刺激器的行为与上一版本相同:

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

SET optimizer_switch = 'derived_merge=off';

这篇关于MySQL 5.7错误(1093:您无法在FROM子句中指定目标表___进行更新)-常规解决方案不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！