Mysql更新关联子查询报错
报错内容:sql 1093 - You can't specify target table 'u' for update in FROM clause
错误原因: if you're doing an UPDATE
/INSERT
/DELETE
on a table, you can't reference that table in an inner query
解决方法: (you can however reference a field from that outer table...)
错误的sql语句
1 update gg_platform_navtree u set u.pid = (select id from gg_platform_navtree s where s.pid = u.pid and s.isleaf = 0 ) where u.isleaf = 1;
通过解决方案转换后的sql语句
update gg_platform_navtree u set u.pid = (select id from (select * from gg_platform_navtree) s where s.pid = u.pid and s.isleaf = 0 ) where u.isleaf = 1;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步