Posted by joelranck on 01/30/07 21:03

I'm trying to update an older table with some new information. Our
previous systems stored US state info by their abbreviation, now have
a state table and link with the appropriate state id. I would like to
update the old table to our new system so set a new field called
state_id to the state id of the state abbreviation in the old table.

UPDATE accounts
SET state_id =
(SELECT states.state_id
FROM states, accounts
WHERE states.state_abbr = accounts.state)

So something similar to above but that is not the correct syntax. I've
done this before in the past but can't remember the syntax for the
life of me.

Thanks for you help guys.

[Back to original message]