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

某天执行了一个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
	)