Mysql突破1093 delete where in 子查询限制

业务场景

假装一张A表存储学生信息，三个字段，id,name,class 假装一张B表存储学生成绩，两个字段，id,score

需求

从A表中删除班级6中成绩低于66的学生

原始SQL

delete from a where id in (select a.id from a INNER JOIN b ON a.id=b.id )

错误信息：

delete from a where id in (select a.id from a INNER JOIN b ON a.id=b.id )
> 1093 - You can't specify target table 'a' for update in FROM clause
> 时间: 0.006s

调整SQL，嵌套子查询

delete from a where id in 
( select id from (select a.id from a INNER JOIN b ON a.id=b.id where b.score<66 ) as n)

正确执行：

delete from a where id in ( select id from (select a.id from a INNER JOIN b ON a.id=b.id where b.score<66 ) as n)
> Affected rows: 2
> 时间: 0.028s

浅析

应该是出于数据保护策略吧，我试过创建视图，也是1093不让删除，不过select语句，子查询不会受影响。

除此之外要删除就只能建立额外字段，先标识，再通过标识去删除了。

通天国际 https://www.aqwdzy.com