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.
x
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.
Advertisement
Answer
UPDATE products
SET discount = 15
WHERE category_id IN (SELECT id FROM product_category WHERE parent_id = 3);