问题描述:
- 以下语句报错:You can’t specify target table ‘branch_relation’ for update in FROM clause
delete from branch_relation
where descendant_id in (
select descendant_id from branch_relation
where ancestor_id = #{id}
)
- 但是下面语句正常运行
delete from branch
where id in (
select descendant_id from branch_relation
where ancestor_id = #{id}
)
原因分析: mysql不允许对同一张表查询之后再来更新删除,而对不同的表可以执行先子查询再进行更新删除。
解决方案: 使用select将子查询再包装一份,如下:
delete from branch_relation
where descendant_id in (
select a.descendant_id from (
select descendant_id from branch_relation
where ancestor_id = #{id}
) as a
)