Search code examples
phpsqlstockinventory

how can i add all product and update a data in another table

i have 2 tables tb1=storage and tb2=daily_quota iwant to add all product with the same name in tb2 and update the finished_product in table1

example table table1=storage

product_name finished_product
Ascorbic Kids 30
Ascorbic Adults 20

table2=daily_quota

product_name finished_product
Ascorbic Kids 30
Ascorbic Adults 30
Ascorbic Kids 30
Ascorbic Adults 30
Ascorbic Kids 30
Ascorbic Adults 30

how can i sum all product_name = Ascorbic Kids.finished_product in table2=daily_quota and update the table1 =storage product_name = Ascorbic Kids finished_product = value of the sum in table2=daily_quota

expected result

table1=storage

product_name finished_product
Ascorbic Kids 90
Ascorbic Adults 90
Solution

  • I would propose to make table1 a view, that you can use as needed.

    The view would look like that:

    CREATE VIEW storage AS
SELECT product_name, SUM(finished_product) as quantity
FROM table2
GROUP BY product_name