SQL错误：Table is specified twice, both as a target for ‘UPDATE‘ and as a separate source for data

错误提示

标题报错的提示语句
Table is specified twice, both as a target for 'UPDATE' and as a separate source for data
翻译过来的意思是:

表被指定两次，一次作为’UPDATE’的目标，一次作为单独的数据源

当然也有的版本提示的不一样，是下面的语句
You can't specify target table for update in FROM clause
意思是：

你不能在FROM子句中指定目标表进行更新

原错误sql：

UPDATE hm_d_plan_apply 
SET del_flag = 2 
WHERE
	uuid IN 
	(
		SELECT
			a.uuid 
		FROM
			hm_d_plan_apply AS a
			LEFT JOIN hm_b_template_plan AS p ON p.uuid = a.apply_plan_id 
		WHERE
		p.del_flag = 2 
	)

解决方法

按照报错提示，可以将子表的查询结果的外层再加一层查询，这样数据源变成了临时表，而非之前联查的两个表。

修复后的sql:

UPDATE hm_d_plan_apply 
SET del_flag = 2 
WHERE
	uuid IN 
	(
		SELECT * FROM 
		(
			SELECT
			a.uuid 
			FROM
				hm_d_plan_apply AS a
				LEFT JOIN hm_b_template_plan AS p ON p.uuid = a.apply_plan_id 
			WHERE
			p.del_flag = 2 
		) AS x
	)