[乐意黎原创]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