我有三个表,productsshopssex。我希望产品表中的DELETE行具有product_id,以使product_id表中不存在等效的sex

此外,对于products值为'www.shop.com'的行,shop_id表中的这些行的shop_id必须与shops表中的shops.shop相等。

到目前为止,我有

DELETE FROM products USING shops WHERE
products.shop_id=shops.shop_id AND
shops.shop='www.shop.com' AND NOT EXISTS
(SELECT sex.product_id FROM sex WHERE
sex.product_id=products.product_id)


但是看来像我一样在子查询中引用products是不可接受的。 (出现错误Unknown table 'products' in MULTI DELETE。)如何解决错误?

最佳答案

您可以在JOIN语句中使用DELETE

DELETE    a
FROM      products a
JOIN      shops b ON a.shop_id = b.shop_id AND b.shop = 'www.shop.com'
LEFT JOIN sex c ON a.product_id = c.product_id
WHERE     c.product_id IS NULL


这只会DELETEshops表中具有相应行且shop = www.shop.com的产品,但前提是该产品在sex表中也没有相应行。

10-05 23:43