如题,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
)