Posted by joelranck on 01/30/07 22:16

Thanks Dick, this worked.

On Jan 30, 1:22 pm, "DickChristoph" <dchrist...@yahoo.com> wrote:
> 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
>
> <joelra...@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]