Adding fields between different tables in Mysql

Asked

Viewed 71 times

1

I have the following structure:

BUYING 

id_compra  
desc_compra  
valortotal_compra  
data_compra  
id_colaborador

COMPRAPROD 

id_compraprod  
id_compra  
id_produto  
qtd_compraprod  
valorunit_compraprod  
valortotal_compraprod

COMPRADAT 

id_compramat  
id_compra  
id_material  
qtd_compramat  
valorunit_compramat  
valortotal_compramat

I would like the field total purchase price, of the PURCHASE table, receive the sum of the values value_compraat and value_compraprod, of the COMPRAAT and COMPRAPROD tables, respectively.

mysql

2 answers

2


You have to get the points in common between the tables, the relationships. Roughly that would be:

update compra set valortotal_compra = (
(select sum(valortotal_compraprod) from COMPRAPROD where id_compra  = :idcompra)+
(select sum(valortotal_compramat)  from COMPRAMAT where id_compra  = :idcompra)
)
where id_compra  = :id_compra

you make two subselect and update to receive

1

Since there is only one field that is common to all tables, id_compra, then you can make a NATURAL JOIN, because it will use this field to do Join. If you do not want to update the entire table at once, then you can add a clause WHERE or replace the NATURAL JOIN for INNER JOIN with clause ON.

UPDATE        compra     c
NATURAL JOIN  compraprod p
NATURAL JOIN  compramat  m
SET c.valortotal_compra = ( SUM(p.valortotal_compraprod) + SUM(m.valortotal_compramat) )

Browser other questions tagged mysql

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