我是否需要在mysql或其他使用PHP的方法中使用总和？

Question

Ok I have a query I need to run in php on my mysql database and just wondering the best way to do it...

I need to add together all the quantities of the product options and use that to get a total for the product quantity.

---

Table product.

In the product table each product has a 'product_id'. Each product contains a column 'quantity' which in most cases is inaccurate.

+-----------+------------+
| ProductID |  Quantity  |
+-----------+------------+
|         1 | 3          |
|         2 | 5          |
|         3 | 1          | 
|         4 | 1          | 
|         5 | 4          |
|         6 | 8          |
|         7 | 2          |
+-----------+------------+

Table product_option_value.

In the product_option_value table each option uses the 'product_id' and contains a column 'quantity' for each option.

+-------------------------+------------+---------------+
| product_option_value_id | product_id |    quanity    |
+-------------------------+------------+---------------+
|            200          |      1     |      12       |
|            200          |      1     |      11       |
|            200          |      1     |      44       |
|            200          |      1     |       4       |
|            204          |      3     |       3       |
|            204          |      3     |       7       |
|            204          |      3     |       9       |
+-------------------------+------------+---------------+

I need to update the 'product' table 'quantity' column with the correct quantities from the 'product_option_value' table with the sum of all the 'quantity' columns where the product_id is the same as the product's product_id.

---

I was thinking making a sum of all the quantities under the option table and using them to update the product table quantity value but I'm not sure the best way to do this using php in mysql?

Answer 1

UPDATE Product
SET Product.Quantity=(
SELECT SUM(product_option_value.Quantity)
FROM product_option_value
WHERE product_option_value.product_id=Product.ProductID
)