【mysql 】sql错误代码 1093 You cannot specify target table xxxx for update in FROM clause

在mysql8.X中执行如下语句：

DELETE 
FROM
	`test`.unsign_wocode 
WHERE
	id IN (


		SELECT
			a.id 
		FROM
			`test`.unsign_wocode a,
			( SELECT wocode, max( in_time ) maxintime FROM `test`.unsign_wocode GROUP BY wocode HAVING count(*) > 1 ) b 
		WHERE
			a.wocode = b.wocode 
			AND a.in_time < b.maxintime 

	);

报如下错误：

DELETE 
FROM
	`test`.unsign_wocode 
WHERE
	id IN (


		SELECT
			a.id 
		FROM
			`test`.unsign_wocode a,
			( SELECT wocode, max( in_time ) maxintime FROM `test`.unsign_wocode GROUP BY wocode HAVING count(*) > 1 ) b 
		WHERE
			a.wocode = b.wocode 
			AND a.in_time < b.maxintime 

	)
> 1093 - You can't specify target table 'unsign_wocode' for update in FROM clause
> 时间: 0.002s

通过去官网mysql错误代码查询错误代码1093得到信息如下：

Error number: 1093; Symbol: ER_UPDATE_TABLE_USED; SQLSTATE: HY000

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 8.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization”.

这个错误发生的原因是在单个sql语句中查询并且修改了同一个表。如果尝试从派生表中查询，可以通过设置系统变量optimizer_switch的derived_merge属性去强迫将子查询的结果存储到临时表，从而避免这种错误发生。

 

下面是解决方案，不是通过上面的方式，而是网上的说法，在子查询外再写一层查询

正确的源代码如下：

 

DELETE 
FROM
	`test`.unsign_wocode 
WHERE
	id IN (
	SELECT
		new.id 
	FROM
		(
		SELECT
			a.id 
		FROM
			`test`.unsign_wocode a,
			( SELECT wocode, max( in_time ) maxintime FROM `test`.unsign_wocode GROUP BY wocode HAVING count(*) > 1 ) b 
		WHERE
			a.wocode = b.wocode 
			AND a.in_time < b.maxintime 
		) new 
	);
	

 

最后，关于临时表、派生表的解释请参考如下地址：

https://blog.csdn.net/weixin_44863976/article/details/102789392