MySQL版本:5.7.20
操作表数据结构如下:
一 问题
将下级部门和上级部门关联,更新下级部门的parent_id 字段为上级部门的id字段值,SQL语句如下:
UPDATE department set parent_id =
(SELECT id from department WHERE department_name='dep_1' LIMIT 1)
where department_level ='2';
报错如下:
[Err] 1093 - You can't specify target table 'department' for update in FROM clause
查看MySQL手册13.2.10.9 Subquery Errors看到:
Unsupported subquery syntax:
Error 1093 (ER_UPDATE_TABLE_USED)
SQLSTATE = HY000
Message = "You can't specify target table 'x'
for update in FROM clause"
也就是说MySQL不支持如下的子查询更新语句:
UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1);
二 解决方法
可以在UPDATE语句中使用子查询进行赋值,因为子查询在UPDATE和DELETE语句以及SELECT语句中都是合法的。但是,不能同时对子查询FROM子句和更新目标使用同一个表(在本例中为表department)。
语句为:
UPDATE department,
(SELECT id from department WHERE department_name='dep_1' LIMIT 1) tmp
set parent_id = tmp.id
where department_level = '2';
更新结果如下:
参考:MySQL :: MySQL 5.7 Reference Manual :: 13.2.10.9 Subquery Errors