报错如题,意思大致是:在一条 sql 语句中不能先查出来部分内容,再同时又对当前表作修改。
解决方法:给查询加别名,用中间表来实现不是对同一表作操作。
如错误定法:
UPDATE xxx_department
SET rank = '2'
WHERE aaa_id in (SELECT id FROM xxx_department WHERE aaa_id IS NULL) ;
改后的写法:
UPDATE xxx_department
SET rank = '2'
WHERE aaa_id in
(SELECT * FROM (SELECT id FROM xxx_department WHERE aaa_id IS NULL) a );
2019. 4.3 后记 :
今天在评论中看到另一方法,特别记录一下。(再度感谢这位愿意在评论中分享解决方法的朋友)
-- sql 原写法:
update x set xxx_xxrial_id = null where id not in
(select id from x where ccc_info = 1);
-- 使用 left join 改写为自链接方式:
update x left join
x a
on x.id = a.id and a.ccc_info = 1
set xxx_xxrial_id = null
where a.id is null;