Need help - sql query to total stock of all attributes to the product

[Chandra]

Requesting help from any sql guru:

I am updating the table tbstockavailable from the backend with stocks of various product attributes.

I would like to update the total stock row (attribute=0) of each product with the sum of the attribute stocks.

Is there a single sql query that I can run at the end of attribute updates to update the totals? Thanks.

[Domas]

join tables tbproductattribute and tbstockavailable by id_product Explain detaily, then can give more code

[Chandra]

@Domas Thanks. The row with attribute 0 is the total row for the product.

The pseudocode would be like: For each product, update quantity where product-attribute=0 as sum of quantity of each product-attribute <> 0

I tried this but this isn't right enough: UPDATE tb_stock_available s1 JOIN tb_stock_available s2 ON s1.id_product = s2.id_product SET s1.quantity = (select sum(quantity) from tb_stock_available s2 where s2.id_product = s1.id_product and s2.id_product_attribute <>0) WHERE s1.id_product_attribute = 0

[datakick]

Try this one:

sql UPDATE tb_stock_available s1 SET s1.quantity = IFNULL(( SELECT SUM(quantity) FROM (SELECT * FROM tb_stock_available) s2 WHERE s2.id_product = s1.id_product AND s2.id_shop = s1.id_shop AND s2.id_product_attribute > 0 ), 0) WHERE s1.id_product_attribute = 0

Because mysql do not support updates with subqueries based on the same table, we have to use the hack with inner (SELECT * FROM tb_stock_available). That will results in temporary table. Wonder why mysql won't do that itself...

[datakick]

One important note - previous query will zero out quantities for products without attributes. If you want to keep those intact, use this update:

UPDATE tb_stock_available s1 SET s1.quantity = IFNULL(( SELECT SUM(quantity) FROM (SELECT * FROM tb_stock_available) s2 WHERE s2.id_product = s1.id_product AND s2.id_shop = s1.id_shop AND s2.id_product_attribute > 0 ), s1.quantity) WHERE s1.id_product_attribute = 0

[Chandra]

@datakick Works perfectly, thanks much.

A related question - do you know if attributewise price is stored in any table (I can't find it) or is calculated on the fly each time based on the impact values?