MySQL update a field using info in other table

Question

How can I make an update of this tables?:

Products table

• IdProd [PK]
• Description
• Cost

Transactions table

• IdTransaction [PK]
• IdProd [FK]
• Quantity
• Total

I want to update the Total quantity finding the Cost in the products table, and multiplying it to my Quantity in my transactions table, but I don't really know how can I do this..

Answer 1

Use Update from JOIN syntax

update Transactions A
INNER JOIN Products B
ON A.IdProd = B.IdProd
set A.Total = A.Quantity * B.cost