471,091 Members | 1,553 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,091 software developers and data experts.

Update Statement

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.

Jan 30 '07 #1
3 5515
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.

Jan 30 '07 #2
Thanks Dick, this worked.

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

Jan 30 '07 #3
(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
Jan 30 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Mark A Framness | last post: by
7 posts views Thread by Dave | last post: by
8 posts views Thread by Lauren Quantrell | last post: by
2 posts views Thread by Mike Leahy | last post: by
1 post views Thread by amitbadgi | last post: by
3 posts views Thread by Michel Esber | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.