Posted by DickChristoph on 01/30/07 21:22

This is not tested but I think it will work in SQL2000

update accounts
set state_id = s.state_id
from accounts a
inner join states s
on a.state = s.state_abbr
--
-Dick Christoph

<joelranck@gmail.com> wrote in message
news:1170191035.465775.79470@v45g2000cwv.googlegroups.com...
> 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]