Table ‘stu’ is specified twice, both as a target for ‘DELETE’ and as a separate source for data

原文链接：Table ‘stu’ is specified twice, both as a target for ‘DELETE’ and as a separate source for data – 每天进步一点点

某天执行了一个sql如下：

1 2 3 4 5 6 7

DELETE FROM stu WHERE id IN (     SELECT cr.id     FROM stu cr     LEFT JOIN app  rt ON cr.id = rt.id     WHERE rt.id IS NULL     )

然后开始报错：

Table ‘stu’ is specified twice, both as a target for ‘DELETE’ and as a separate source for data

报错原因很简单，同一个表被指定了两次，同时作为查询和删除的数据源。

解决方法：

讲内层表变为临时表即可，如下：

1 2 3 4 5 6 7 8 9 10

DELETE FROM stu WHERE id IN (     SELECT * from (     SELECT cr.id     FROM stu cr     LEFT JOIN app  rt ON cr.id = rt.id     WHERE rt.id IS NULL     )as p     )