You can't specify target table 'a' for update in FROM clause

错误:You can't specify target table 'a' for update in FROM clause

含义:不能在同一表中查询的数据作为同一表的更新数据。

工具:MySQL

数据:

id      父级id  编号   父级编号

1      3          a          b

3     5        b          c

业务场景:通过自己的父级编号查询到父级的id修改自己的父级id.

错误sql:

update base_staff as a set directSupervisor = (select uuid from base_staff where originDataId =a.hlLeader  ) where hlLeader is not null

正确sql:

update base_staff as a set directSupervisor = ( select uuid 
 from ( select uuid,originDataId,hlLeader from base_staff ) b where b.originDataId=a.hlLeader)
  where hlLeader is not null;