SQL UPDATE base on COUNT

I have two tables

• product-table with fields (id, category_id)
• category-table with fields (id, num_of_products)

The product table contains multiple products in different categories. And I know how to count product in each categories using

SELECT category_id, COUNT(product.id) as count FROM product GROUP BY category_id

But how do I update category.num_of_product using a single sql query with the result of the SELECT …. ? I have tried but can not figure out the proper syntax !!

UPDATE c
SET num_of_products = count
FROM category c
INNER JOIN 
(SELECT category_id as id, COUNT(product.id) as count FROM product GROUP BY category_id) d ON c.id = d.id

Any suggestions ?

Answer

Try this query:

UPDATE category c 
JOIN (SELECT category_id, COUNT(id) cnt 
        FROM product GROUP BY category_id) p
ON c.id=p.category_id
SET c.num_of_products=cnt;

If you want to do UPDATE with JOIN, you must do the JOIN first then followed with SET. If there are conditions then you need to add WHERE after the SET. For example, let’s say there are some category you don’t want to update then probably:

UPDATE category c 
JOIN (SELECT category_id, COUNT(id) cnt 
        FROM product GROUP BY category_id) p
ON c.id=p.category_id
SET c.num_of_products=cnt
WHERE c.id NOT IN (1,2);

Demo fiddle