How to do an update on a table by traversing another table in Mysql? [closed]

Navigation
0

I have a schematic according to the picture below:

The goal is to update the values of the seller_comission column in the tbl_history table. The tbl_history table already has a few thousand records. The seller's commission is different for each product and the value is registered in the tbl_plans table. The tbl_prod_plan table stores the relationship between plans and products.

Could do loops with php and solve the problem. But the question is how could this update be done just by using sql. The database is MySql.

    
asked by anonymous 11.09.2017 / 14:48

1 answer

0

Your modeling is somewhat confusing, but if you want to make a UPDATE based on another table, just use the FROM clause with alias and JOIN , together with GROUP BY per code. product and SUM to get the total sum of the column of the other table: 

UPDATE th
   SET th.seller_comission = SUM(tp.seller_comission)
  FROM tbl_history th
       INNER JOIN tbl_prod_plan tpp ON tpp.prod = th.prod
       INNER JOIN tbl_plans tp ON tp.id = tpp.id_plan
 GROUP BY th.prod
    
11.09.2017 / 14:59
Reload with Jquery without cache (Chrome) PHP / MySQL Select products below a value in the table