mysql - 根据其他 2 个值从 mysql 表中删除

Question

我正在尝试从表 rk21a_virtuemart_product_categories 中删除，其中 virtuemart_category_id = 251 和 product_discount_id 来自 rk21a_virtuemart_product_prices = 0

尝试过此查询但不起作用(删除 virtuemart_category_id =251 的所有内容，但不是两个条件)

DELETE FROM rk21a_virtuemart_product_categories
   WHERE virtuemart_category_id = 251
   AND virtuemart_product_id IN (
   SELECT virtuemart_product_id
   FROM rk21a_virtuemart_product_prices
   WHERE product_discount_id = 0)

表格是:

**rk21a_virtuemart_product_categories**
id   virtuemart_product_id  virtuemart_category_id
12   119                    160
13   121                    251
15   122                    173
20   123                    251

和

**rk21a_virtuemart_product_prices**
virtuemart_product_price_id virtuemart_product_id product_discount_id
1021                         119                  5
1022                         121                  0
1023                         122                  0
1024                         123                  10

有人可以告诉我我做错了什么吗？我是初学者..谢谢，

Answer 1

试试这个:

DELETE c
FROM rk21a_virtuemart_product_categories AS c
INNER JOIN rk21a_virtuemart_product_prices AS p 
        ON c.virtuemart_product_id  = p.virtuemart_product_id
WHERE c.virtuemart_category_id = 251
  AND p.product_discount_id = 0;

demo

在第一个表rk21a_virtuemart_product_categories中有两行类别id = 251，但在第二个表中只有一行.product_discount_id = 0，所以只会删除一行。