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.