sql 报错：Table is specified twice, both as a target for 'UPDATE' and as a separate source for data

如题，sql 报错:Table is specified twice, both as a target for 'UPDATE' and as a separate source for data。表被指定了两次，同时作为 update 对象和独立数据源。

报错场景：查询两个表的差集并更新记录。举例说明：a、b 两表联查，找出 a 表中存在 b 表不存在的记录，然后更新 a 表的某个字段做标记。

报错 sql：

UPDATE company AS f SET related = 0 WHERE uid IN 
(
select c.uid FROM company AS c 
LEFT JOIN member AS m ON m.uid=c.uid 
WHERE m.uid IS NULL
)

解决方法

既然一个表不能同时作为 update 对象和独立数据源，那就改变其中一个。update 为主句，不可能去掉，那就只能修改作为数据源部分的表。将两表联查的结果作为一个临时表，在外层在加一层查询。这样数据源变成了临时表，而非之前联查的两个表。

UPDATE company AS f SET related = 0 WHERE uid IN (
SELECT * FROM 
(
select c.uid FROM company AS c 
LEFT JOIN member AS m ON m.uid=c.uid 
WHERE m.uid IS NULL
) AS d
)