我有三个表,products
,shops
和sex
。我希望产品表中的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
这只会
DELETE
在shops
表中具有相应行且shop
= www.shop.com的产品,但前提是该产品在sex
表中也没有相应行。