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

原文链接：https://www.longkui.site/error/table-stu-is-specified-twice-both-as-a-target-for-delete-and-as-a-separate-source-for-data/5730/

某天执行了一个sql如下：

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

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

解决方法：

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

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
)