mysql 1093 - You can't specify target table 'xx表' for update in FROM clause
为了修复节点表某批次数据的用户数据,做出了以下尝试:
UPDATE zs_work_approval_node SET uid = 4963, name = '苏畅' WHERE id IN (SELECT id FROM zs_work_approval_node WHERE uid = 4967 AND STATUS IN (0, 1, 4));
执行:[Err] 1093 - You can't specify target table 'zs_work_approval_node' for update in FROM clause
百度查询提示得知:
update语句中包含的子查询的表和update的表为同一张表时,报错:1093-You can’t specify target table for update in FROM clause
mysql不允许update目标表和子查询里面的表为同一张表
解决办法: 利用子查询sql可以改变双层的子查询,即可执行成功 (但性能较差,仅仅适合较小的数据量的)
UPDATE zs_work_approval_node SET uid = 4963, name = '苏畅' WHERE id IN (SELECT * FROM ( SELECT m.id FROM zs_work_approval_node m WHERE uid = 4967 AND STATUS IN (0, 1, 4)) AS temp);
将来不迎,当下不杂,过往不念。