Posted by Erland Sommarskog on 01/30/07 22:35

(joelranck@gmail.com) writes:
> 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 can't escape the comment that this does not sound like the best design
to me. Having a table for states is a good idea, but the well-established
two-letter codes are much better for keys than an 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.

Just remove "accounts" from the correlated subquery, and you have an
ANSI-compliant solution.

Dick's solution works well too.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]