Update value in one table with result from another

Question

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

You need multiple table syntax - see here: http://dev.mysql.com/doc/refman/5.0/en/update.html — AgRizzo, Oct 14 '13 at 13:18

score 1 · Answer 1 · answered Oct 14 '13 at 13:20

1

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

answered Oct 14 '13 at 13:20

Alma Do

36,374
9
70
101

It should be **`inner join`** – Oct 14 '13 at 13:22

bazzilic · Answer 2 · 2014-11-10T12:14:33.063

1

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.

edited Nov 10 '14 at 12:14

answered Oct 14 '13 at 13:21

bazzilic

796
2
6
20

score 0 · Answer 3 · answered Oct 14 '13 at 13:21

0

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

answered Oct 14 '13 at 13:21

This is the SQL-Server `UPDATE..FROM` syntax. It won't work in MySQL. – GarethD Oct 14 '13 at 13:24

Update value in one table with result from another

3 Answers3