CODEWITHSUNDEEP
mysqljoin
Alain.D
Alain.D

Reputation: 308

How to Update column data by doing a SUM of values from a second table depending of ID from a third one?

I have three tables as follow (can see in attachment too)

product_attribute_shop

+----------------------+--------+------------+
| id_product_attribute | price  | id_product |
+----------------------+--------+------------+
| 27934                | 50.000 | 9779       |
+----------------------+--------+------------+
| 27935                | 24.000 | 9780       |
+----------------------+--------+------------+

attribute_impact

+------------+--------------+--------+
| id_product | id_attribute | price  |
+------------+--------------+--------+
| 9779       | 45           | 00.000 |
+------------+--------------+--------+
| 9779       | 46           | 00.000 |
+------------+--------------+--------+
| 9779       | 47           | 00.000 |
+------------+--------------+--------+
| 9779       | 55           | 00.000 |
+------------+--------------+--------+
| 9779       | 56           | 46.000 |
+------------+--------------+--------+
| 9779       | 57           | 67.000 |
+------------+--------------+--------+

product_attribute_combination

+--------------+----------------------+
| id_attribute | id_product_attribute |
+--------------+----------------------+
| 46           | 27934                |
+--------------+----------------------+
| 56           | 27934                |
+--------------+----------------------+
| 46           | 27935                |
+--------------+----------------------+
| 57           | 27935                |
+--------------+----------------------+

How can I update the price of table product_attribute_shop.
For each id_product_attribute the price should be equal to the sum of price attributes from table attribute_impact when this attributes share the same id_product_attribute ?

Edit:

Here are an exemple of my 3 tables :

product_attribute_combination

attribute_impact

product_attribute_shop

I want to update the price of product_attribute_shop where id_product_attribute = 27934. For this i need to get the matching attributes in product_attribute_combination (looking for the same id_product_attribute) and then make the sum of the price of this attributes from table attribute_impact.

In product_attribute_shop where id_product_attribute = 27934, the price should be (46.0 + 0.00) = 46.0 instead of 50.00.

ANSWER : To edit all combinations price, I used the following query :

UPDATE product_attribute_shop
SET product_attribute_shop.price =
(
   SELECT SUM(attribute_impact.price)
   FROM attribute_impact
   WHERE product_attribute_shop.id_product = attribute_impact.id_product
         AND attribute_impact.id_attribute IN
         (
            SELECT product_attribute_combination.id_attribute
            FROM product_attribute_combination
            WHERE product_attribute_combination.id_product_attribute =
                  product_attribute_shop.id_product_attribute
         )
);

Upvotes: 0

Views: 57

Answers (1)

Charith
Charith

Reputation: 88

You can use a nested query to achieve what you want.

You could use a query like this if you want to update an individual product

update product_attribute_shop set price = (select sum(price) from attribute_impact where id_attribute in (select id_attribute from product_attribute_combination where id_product_attribute = 27934)) where id_product_attribute = 27934;

More note: In the attribute_impact table, the id_product is duplicate information which could easily be fetched with a join from the table product_attribute_shop. You could probably remove if not needed for any other special purpose.

Upvotes: 1

Related Questions