我有两个表
- 包含字段(id,category_id(的产品表
- 包含字段(id、num_of_products(的类别表
产品表包含不同类别的多个产品。我知道如何使用计算每个类别中的产品
SELECT category_id, COUNT(product.id) as count FROM product GROUP BY category_id
但是,如何使用SELECT…的结果使用单个sql查询来更新category.num_of_product?我试过了,但找不出正确的语法!!
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
有什么建议吗?
尝试此查询:
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;
如果要用JOIN
执行UPDATE
,则必须先执行JOIN
,然后再执行SET
。如果有条件,则需要在SET
之后添加WHERE
。例如,假设有一些类别你可能不想更新:
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);
演示小提琴