CODEWITHSUNDEEP
mysqlsql
harryg
harryg

Reputation: 24107

Update value in one table with result from another

OK I have 2 tables:

holdings: (id, long_name, value, date, sedol)
asset_key: (id, long_name, sedol)

My issue is that in holdings there are many records where the sedol wasn't filled in. I have the asset_key table however that maps a given long_name to a sedol.

Is there a query that can populate holdings.sedol with the result from asset_key?

Something like:

UPDATE holdings SET holdings.sedol = 
    SELECT asset_key.sedol FROM asset_key 
    WHERE sedol.long_name = asset_key.long_name

Upvotes: 1

Views: 129

Answers (3)

bazzilic
bazzilic

Reputation: 828

This should work:

UPDATE `holdings`
SET `holdings`.`sedol` = (SELECT `asset_key`.`sedol`
                          FROM   `asset_key`
                          WHERE  `asset_key`.`long_name` = `holdings`.`long_name`)

However, if I am not wrong, you should be sure that this SELECT subquery returns only one row or MySQL will throw an error.

Upvotes: 1

user2464660
user2464660

Reputation:

Try the below Query:

update holdings 
SET holdings.sedol = asset_key.sedol
from holdings
inner join  asset_key on sedol.long_name = asset_key.long_name

Note: The inner join should result in single value only

Upvotes: 0

Alma Do
Alma Do

Reputation: 37365

This will do the trick:

UPDATE 
  holdings 
    LEFT JOIN asset_key ON sedol.long_name = asset_key.long_name 
SET 
  holdings.sedol=asset_key.sedol

Upvotes: 1

Related Questions