【MySQL异常】1093 - You can‘t specify target table ‘daily_job‘ for update in FROM clause

一、背景描述

通过sql语句想把这个表里的数据查询出来，然后根据查询出来的id把同一张表里的数据删除，

如下是会报错的sql语句：

DELETE 
FROM
  daily_job 
WHERE
  id IN ( SELECT id FROM daily_job WHERE create_user = '***' );

在执行 mysql 语句时，出现 1093 - You can't specify target table 'daily_job' for update in FROM clause 这个错误。

二、错误原因

出现 1093 - You can't specify target table 'daily_job' for update in FROM clause 这个错误，它表面的意思是不能在同一个sql语句中，先select同一个表的某些值，然后再update这个表。

其实原因就是MySQL不支持，在一条语句对同一个表，先查询再更新的操作。

官方说明：

Error: 1093 SQLSTATE: HY000 (ER_UPDATE_TABLE_USED)

Message: You can't specify target table '%s' for update in FROM clause

This error occurs for attempts to select from and modify the same table within a single statement. If the select attempt occurs within a derived table, you can avoid this error by setting the derived_merge flag of the optimizer_switch system variable to force the subquery to be materialized into a temporary table, which effectively causes it to be a different table from the one modified. See Section 9.2.2.3, “Optimizing Derived Tables and View References”.

译文：当试图在单个语句中选择和修改相同的表时，会发生此错误。如果选择尝试发生在派生表中，您可以通过设置optimizer_switch系统变量的derived_merge标志来避免此错误，以强制将子查询实体化到一个临时表中，这实际上会导致它与被修改的表不同。参见9.2.2.3节，“优化派生表和视图引用”。

三、解决方案

以下提供两种解决方案，可根据具体情况使用某一种方法解决：

3.1 解决方案1：修改SQL语句

比如：利用临时表

DELETE 
FROM
  daily_job 
WHERE
  id IN ( SELECT a.id FROM ( SELECT * FROM daily_job WHERE create_user = '***' ) a );

3.1 解决方案2：设置optimizer_switch的 derived_merge参数

官方说明里有一种方法即可以通过设置 optimizer_switch 的 derived_merge参数来解决。不过呢，通过官方的这种方法实际上会导致它与被修改的表不同。

四、拓展

Oracle和SQLServer数据库，不会这个问题。

完结！