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

转载于:https://www.cnblogs.com/ghjbk/p/10456204.html