解决MySQL报错:You can‘t specify target table ‘region‘ for update in FROM clause

吾爱主题阅读：135 2023-03-20 14:49:00 评论：0

前言

首先明确一点这个错误只会发生在delete语句或者update语句，拿update来举例 : update A表 set A列 = (select B列 from A表)； 这种写法就会报这个错误，原因：你又要修改A表，然后又要从A表查数据，而且还是同层级。Mysql就会认为是语法错误！

嵌套一层就可以解决，update A表 set A列 = (select a.B列 from (select * from A表) a); 当然这个只是个示例，这个示例也存在一定的问题，比如(select a.B列 from (select * from A表) a)他会查出来多条，然后赋值的时候会报 1242 - Subquery returns more than 1 row。

嵌套一层他就可以和update撇清关系，会优先查括号里面的内容，查询结果出来过后会给存起来，类似临时表，可能有的人该好奇了，update A表 set A列 = (select B列 from A表)； 我明明加括号了呀，难道不算嵌套吗，当然不算，那个括号根本没有解决他们之间的层次关系！

所谓层次关系就是一条sql当中谁先执行谁后执行，能理解层次关系的尽量要理解，不懂也没关系，下面我提供了四个案例供大家参考！

示例一

以这张表为例：

1 2 3 4 5 6 7 8 9 10 DROP TABLE IF EXISTS `region`; CREATE TABLE `region` ( `Id` int (11) NOT NULL COMMENT '主键id' , ` Name ` varchar (40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名称' , `Pid` int (11) NULL DEFAULT NULL COMMENT '父类id' , `status` varchar (2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '1' COMMENT '1:启用,2:禁用' , PRIMARY KEY (`Id`) USING BTREE, INDEX `FK_CHINA_REFERENCE_CHINA`(`Pid`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '省市区表' ROW_FORMAT = DYNAMIC ;

错误用法：

这个例子就是典型，我要修改region表数据，要将pid 赋值为region当中的某一列数据，这种写法就会报错！mysql不支持 同一张表当中既要修改又要查询。

1	`UPDATE` `region` `SET` `pid = (` `select` `pid` `FROM` `region` `WHERE` `NAME` `=` `'市辖区'` `)` `where` `name` `=` `'北京'` `;`

明确一点，这种的不是同一张表是不会报错的！

1	`UPDATE` `region` `SET` `pid = (` `select` `id` `FROM` `banner) ;`

正确用法：

1 2 3 4 UPDATE region SET pid = ( SELECT a.pid FROM ( SELECT Pid FROM region WHERE NAME = '市辖区' ) a ) WHERE NAME = '北京' ;

示例二

错误用法：

1 2 3 4 UPDATE region a SET Name = '1' WHERE a.pid IN ( SELECT id FROM region WHERE NAME = '市辖区' );

正确用法：

1 2 3 4 UPDATE region a SET Name = '1' WHERE a.pid IN ( SELECT b.id FROM ( select * FROM region) b WHERE b. NAME = '市辖区' );

示例三

这个sql没有错误示例，只有正确示例，相对来说比较复杂点！

1 2 3 4 5 6 7 8 9 10 11 UPDATE region a SET pid = ( SELECT b.pid FROM ( SELECT id, pid FROM region b WHERE b. NAME = '市辖区' ) b WHERE a.Pid = b.id ) WHERE a.pid IN ( SELECT c.id FROM ( SELECT * FROM region ) c WHERE c. NAME = '市辖区' );

示例四

再来看一个删除的示例

错误用法：

1 2 3 4 5 6 7 8 delete from tbl where id in ( select max (id) from tbl a where EXISTS ( select 1 from tbl b where a.tac=b.tac group by tac HAVING count (1)>1 ) group by tac );

正确用法：

1 2 3 4 5 6 7 8 9 10 11 delete from tbl where id in ( select a.id from ( select max (id) id from tbl a where EXISTS ( select 1 from tbl b where a.tac=b.tac group by tac HAVING count (1)>1 ) group by tac ) a );