CODEWITHSUNDEEP
mysqlsqldatabaseexcel
MrSilent
MrSilent

Reputation: 574

MySQL. Auto-add in another table

I have two tables:

table1:

id | product   | quantity | price_per_unit
1    tshirt         5             10
2    pants         10             20
3    chocolate     30              5
4    juice         15              6
5    nutella       20             15

table2:

id | product_id | quantity_sold | price
1        1              2           ?
2        2              1           ?
3        3              5           ?

Obviously.. the price would be 20 for 2 sold t-shirts, 20 for 1 sold pants and 25 for 5 chocolates. But how can I achieve that without having to type it myself? I have thought of joining the both tables and table2.price = quantity_sold * price_per_unit but I don't really have any idea how to do that. Same as doing table2.D1 = table2.C1 * table1.D1, table2.D2 = table2.C2 * table1.D2 etcetera Tips?

Upvotes: 1

Views: 33

Answers (1)

sgeddes
sgeddes

Reputation: 62831

I think you're looking for an UPDATE with a JOIN:

update table2 t2
  join table1 t1 on t2.product_id = t1.id
set t2.price = t1.price_per_unit * t2.quantity_sold;

Upvotes: 1

Related Questions