How to update a table by going through another table in Mysql?

Asked

Viewed 318 times

0

I have a scheme according to the picture below: inserir a descrição da imagem aqui

The goal is to update the values of the seller_comission column in the table tbl_history. The table tbl_history already has a few thousand records. The sales commission is different for each product and the value is recorded in the tbl_plans table. Table tbl_prod_plan stores the list of plans and products.

You could make loops with php and solve the problem. But the question is how could this update be done only using sql. The database is Mysql.

mysql sql sql-update
  • 1

    I didn’t understand a thing...

  • 2

    I don’t understand anything...

1 answer

0


Your modeling is a bit confusing, but if you want to make a UPDATE based on another table, just use the clause FROM as an alias and JOIN, together with the GROUP BY by product code and SUM to achieve 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

  • I used a variation of your answer and it worked. UPDATE history AS th 
INNER JOIN tbl_prod_plan AS tpm ON LOWER(tpm.prod) = LOWER(th.prod) 
INNER JOIN tbl_plans AS tp ON tp.id = tpm.id
SET th.seller_commission = tp.seller_commission;

  • @zwitterion Beleza. If the answer met you do not forget to mark it as chosen so that other people with similar doubt can benefit from the answer.

  • 1

    Note, it’s kind of confusing because I’m not using the real fields and in full. The model has about 200 tables and many of them have an average of 25 fields. I only used the fields I thought were important for the question. And another thing, in this company it is forbidden to use Primary key. A particularity that is not part of the question but can hinder the general idea.

  • @zwitterion just out of curiosity: why it is forbidden to use primary key?

  • There is no technical reason (as I see it). They fear someone deleting 1 record by accident and leaving deleting cascading. This is what the company’s CTO thinks. That is why it is forbidden to use PK in table relations.

Browser other questions tagged mysql sql sql-update

You are not signed in. Login or sign up in order to post.