By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,412 Members | 1,630 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,412 IT Pros & Developers. It's quick & easy.

Update Statement

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
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

P: n/a
(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.