SELECT subCategory which equals parentCategory = value from outer table

I have a products table and I need to put discount on some products from a given sub category, which has parent category in another table.

TABLE products:

category_id      sku      discount     
-----------------------------------
8             3141          NULL 
7             4333          NULL 
7             601           NULL
3             405           NULL  


TABLE product_category:

id      parent_id           slug     
-----------------------------------
8             18          somename 
7             3           somename

It’s a pretty simple question but I can’t figure out how to use the parent_id column to update my products table simply, instead of writing down every subcategory id like this:

UPDATE products 
SET discount = 25 
WHERE category_id IN (1,4,5,6 etc..);

I want to update the products table by selecting the subcategory’s parent id from the product_category table.

Answer

UPDATE products 
SET discount = 15
WHERE category_id IN (SELECT id FROM product_category WHERE parent_id = 3);

Advertisement

Answer