[乐意黎原创]MySQL 中 You can't specify target table 'table_name' for update in FROM clause错误解决办法

如下图,当执行更新时

update `table_aerchi` as a
    set `biaohao` = (
        select count(*) from `table_aerchi` as b
        where b.`cuncode` ='532523101003' and b.`cunxuhao` ='01'
    )
where a.`cuncode` ='532523101003' and a.`cunxuhao` ='01'

抛错误,即: 失败原因:You can't specify target table 'outer_tb' for update in FROM clause

解决方法:select的结果再通过一个中间表select多一次,就可以避免这个错误

修改如下, table_aerchi 再用一次, 即用  (select * from `table_aerchi`) 来表示 table_aerchi

update `table_aerchi` as a
    set `biaohao` = (
        select count(*) from (select * from `table_aerchi`) as b
        where b.`cuncode` ='532523101003' and b.`cunxuhao` ='01'
    )where a.`cuncode` ='532523101003' and a.`cunxuhao` ='01'

执行后,成功。 提示: 

执行成功,影响行数:[1],耗时:[141ms.]

乐意黎

2019-07-21

发布了430 篇原创文章 · 获赞 415 · 访问量 925万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 编程工作室 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览