【MySQL异常】MySQL出现 You can‘t specify target table for update in FROM clause 错误的解决方法

一、背景描述

MySQL 数据库，版本号为 5.7.37（语句为 SELECT VERSION();）

在 mysql 数据库中有一张表为 device_data_monitor，需求是更改这个表中的某一列的值。于是就使用以下语句更新 pillar_id 这一列的值。

UPDATE device_data_monitor 
SET pillar_id = 29 
WHERE
	monitor_id IN ( SELECT monitor_id FROM device_data_monitor WHERE pillar_id = 10 );

结果就会报错，内容如下：

You can‘t specify target table for update in FROM clause 结果如下：

二、原因分析

在MySQL中，可能会遇到 You can’t specify target table ‘表名’ for update in FROM clause 这样的错误

它的意思是说，不能在同一语句中，先 select出同一表中的某些值，再 update这个表，即不能依据某字段值做判断再来更新某字段的值。

这个问题在MySQL官网中有提到解决方案：MySQL-UPDATE- 拉到文档下面

You can’t specify target table ‘表名’ for update in FROM clause 这样的错误的原因就是因为MySQL不支持在子查询中引用更新目标表。

我们要更新的就是 device_data_monitor 这张表，而在子查询的 FROM 语句中还从这张表查询出数据，这在MySQL中是不被允许的。之所以有这个规定，也是考虑到了数据安全。

三、解决方案

解决方法： select的结果再通过一个中间表 select多一次，就可以避免这个错误。

UPDATE device_data_monitor 
SET pillar_id = 29 
WHERE
	monitor_id IN ( SELECT monitor_id FROM (SELECT monitor_id FROM device_data_monitor WHERE pillar_id = 10) a );

四、踩过的坑

最里面的 SELECT 语句查询出来的结果当作虚拟表，注意：此虚拟表一定要有别名，否则就会报另外的错误，如下图所示：

出现此错误的解决方案就是 子查询语句中的 虚拟表 添加别名。 比如解决方案中的 SQL语句里有个 别名为 a 。

本文完结！