Update Statement

[joelranck]

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.

[DickChristoph]

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

<jo*******@gmail.comwrote in message
news:11*********************@v45g2000cwv.googlegro ups.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.

[joelranck]

Thanks Dick, this worked.

On Jan 30, 1:22 pm, "DickChristoph" <[e-mail]:

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

<[e-mail] in message

news:11*********************@v45g2000cwv.googlegro ups.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.

[Erland Sommarskog]

(jo*******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx