CODEWITHSUNDEEP
postgresqlsql-update
Uniextra
Uniextra

Reputation: 143

Update column based on select from another table

Im traying to update a table based on a slect from another table. Both tables have in common the name coll.

so the idea is grom hitorial to get the min price of each name, and update those to every row in names that mactch the name.

I have tried several examples found here, but none did the trick.

This is the inner select

SELECT name, MIN(price) FROMhistorial group by name

this should update a 

UPDATE names set MIN_PRICE = min_price_from_select WHERE name = name_from_select

result should be, the table Names updated with the min prices from historial.

Upvotes: 1

Views: 622

Answers (1)

Bergi
Bergi

Reputation: 664247

You can join an UPDATE statement with a FROM clause. In your case:

UPDATE names
SET min_price = q.min_price
FROM (
  SELECT name, MIN(price) as min_price
  FROM historical
  GROUP BY name
) as q
WHERE name = q.name

The alternative would be a subselect, but that would also try to update all names, not only those that exist in historical:

UPDATE names
SET min_price = (SELECT MIN(price) FROM historical WHERE historical.name = names.name)

Upvotes: 2

Related Questions