CODEWITHSUNDEEP
mysqlsql-update
Velu Subramanian
Velu Subramanian

Reputation: 47

Update using data from another table

I want the minimum price from Table 2 to be filled in price column of Table 1 for a particular id.

Table 1

pid price
111 0
222 0
333 0

Table 2

pid price
111 100
111 200
222 120
222 90
333 200
333 150

Expected output: Table 1

pid price
111 100
222 90
333 150

Upvotes: 1

Views: 35

Answers (2)

vishal
vishal

Reputation: 17

this is query to get lowest price from table2 (SELECT price FROM table2 WHERE price = ( SELECT MIN(price) FROM table2 ) ) now you can update the table 1 ( update table1 set price="result you got from above query" where id=given id)

Upvotes: 0

FyodorX
FyodorX

Reputation: 1480

You would do something like:

UPDATE Table1 t 
SET t.price = (SELECT MIN(t2.price) FROM Table2 t2 WHERE t2.pid = t.pid);

Upvotes: 1

Related Questions