记录一个多表查询的问题 #1093 - You can‘t specify target table ‘xxx‘ for update in FROM clause

SQL里面，多表查询比较常见，用法也比较简单

可以是

SELECT 
ood.oodId, ood.status, ood.userId, orders.createdAt 
FROM 
ood, orders
WHERE 
ood.userId = orders.userId and orders.state = 2

当然也可以使用JOIN。

但是如果要update，这里有个问题，如果直接写

UPDATE 
ood 
SET 
ood.status = 0 
WHERE 
oodId NOT IN (SELECT ood.oodId FROM ood, orders WHERE ood.userId = orders.userId and orders.state = 2)

会报错误，#1093 - You can't specify target table 'ood' for update in FROM clause，搜了一下大概意思是That is, if you're doing an UPDATE/INSERT/DELETE on a table, you can't reference that table in an inner query (you can however reference a field from that outer table...)

不能修改一个在query中引用的表，不过我们有办法绕过去。

将被引用的表换个名字就可以了。如下：

UPDATE 
ood 
SET 
ood.status = 0 
WHERE 
oodId NOT IN (SELECT oodId FROM (SELECT * FROM ood) as ood_b, orders WHERE ood_b.userId = orders.userId and orders.state = 2)

将ood换成(SELECT * FROM ood) as ood_b，起个别名就可以了。

感谢 https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause