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

浅浅的花香味﹌ 2023-02-13 05:49 132阅读 0赞

在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 
    
    	);

报如下错误：

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

Error number: `1093`; Symbol: [`ER_UPDATE_TABLE_USED`][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`][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”][Section_8.2.2.4_ _Optimizing Derived Tables_ View References_ and Common Table Expressions with Merging or Materialization].

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

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Nvb2xjb2ZmZWUxNjg_size_16_color_FFFFFF_t_70][]

正确的源代码如下：

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][https_blog.csdn.net_weixin_44863976_article_details_102789392]

[mysql]: https://dev.mysql.com/doc/refman/8.0/en/server-error-reference.html
[ER_UPDATE_TABLE_USED]: https://dev.mysql.com/doc/refman/8.0/en/server-error-reference.html#error_er_update_table_used
[optimizer_switch]: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_optimizer_switch
[Section_8.2.2.4_ _Optimizing Derived Tables_ View References_ and Common Table Expressions with Merging or Materialization]: https://dev.mysql.com/doc/refman/8.0/en/derived-table-optimization.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Nvb2xjb2ZmZWUxNjg_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2020052718223234.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Nvb2xjb2ZmZWUxNjg=,size_16,color_FFFFFF,t_70
[https_blog.csdn.net_weixin_44863976_article_details_102789392]: https://blog.csdn.net/weixin_44863976/article/details/102789392