CODEWITHSUNDEEP
sqlpostgresql
darkpool
darkpool

Reputation: 14641

Update table from column of a different table

I have the following two tables (just showing the parts that are relevant):

symbol:

symbol, exchange_id, sector, <several other columns>
abc,    1,           xyz
arc,    2,           dkg

sector:

symbol, sector
abc,    mno

The sector tables has all the correct sectors per symbol in it. I need to copy all the sectors from the sector table into the symbol table (overwriting any existing values) where the symbol in both tables match, but only where exchange_id=1

I have tried various options with no luck. I would have thought the following options would work but I get the error on both: table name 'symbol' specified more than once:

UPDATE symbol
SET symbol.sector = sector.sector
FROM symbol, sector
WHERE symbol.symbol = sector.symbol
AND symbol.exchange_id=1

UPDATE symbol
SET    symbol.sector = sector.sector
FROM   symbol
JOIN   sector ON symbol.symbol = sector.symbol
WHERE  symbol.exchange_id=1

I am using postgresql.

Upvotes: 1

Views: 43

Answers (1)

fthiella
fthiella

Reputation: 49049

You can write your query this way:

UPDATE symbol s
SET    sector=t.sector
FROM   sector t
WHERE  s.symbol=t.symbol

Please see a fiddle here.

Upvotes: 1

Related Questions