May 5, 2011 at 12:35 pm
I'm trying to update an Address table with a state based on a provided zip code. The select works. I can see the correct state value and even the row to change (if PK included in the select statement) but the update statement throws errors.
UPDATE ADDRESS
SET ADDRESS_STATE_ID =
(SELECT s.STATE_ID FROM ADDRESS a
INNER JOIN zipcode z
ON a.ADDRESS_ZIPCODE_TXT = z.ZIP_CD
INNER JOIN state_ref s
ON s.STATE_ABBREV = z.STATE_CD
WHERE ADDRESS_STATE_ID IS NULL AND ADDRESS_ZIPCODE_TXT IS NOT NULL)
What am I missing?
Thanks.
May 5, 2011 at 12:50 pm
not sure, i'm not comfortable with teh UPDATE from a subselect, so i'd use this update statement instead:
UPDATE ADDRESS
SET ADDRESS_STATE_ID = s.STATE_ID
FROM zipcode z
INNER JOIN state_ref s
ON s.STATE_ABBREV = z.STATE_CD
WHERE ADDRESS.ADDRESS_ZIPCODE_TXT = z.ZIP_CD
AND ADDRESS_STATE_ID IS NULL
AND ADDRESS_ZIPCODE_TXT IS NOT NULL
Lowell
May 5, 2011 at 12:51 pm
Your update is using a subselect. My guess is the error is that a subquery can't return more than 1 value??
Try this:
UPDATE ADDRESS
SET ADDRESS_STATE_ID = s.STATE_ID
FROM ADDRESS a
INNER JOIN zipcode z
ON a.ADDRESS_ZIPCODE_TXT = z.ZIP_CD
INNER JOIN state_ref s
ON s.STATE_ABBREV = z.STATE_CD
WHERE ADDRESS_STATE_ID IS NULL AND ADDRESS_ZIPCODE_TXT IS NOT NULL
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 5, 2011 at 12:51 pm
Lowell you beat me to the punch again. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 5, 2011 at 12:52 pm
I would use this update statement instead
UPDATE ADDRESS
SET ADDRESS_STATE_ID = s.STATE_ID
FROM ADDRESS a
INNER JOIN zipcode z
ON a.ADDRESS_ZIPCODE_TXT = z.ZIP_CD
INNER JOIN state_ref s
ON s.STATE_ABBREV = z.STATE_CD
WHERE ADDRESS_STATE_ID IS NULL
AND ADDRESS_ZIPCODE_TXT IS NOT NULL
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 5, 2011 at 12:53 pm
Sean Lange (5/5/2011)
Lowell you beat me to the punch again. 😛
Curses - i was in the reply window when you guys posted.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 5, 2011 at 12:53 pm
Sean Lange (5/5/2011)
Lowell you beat me to the punch again. 😛
I'm hitting a lot of low hanging fruit today! I'll leave complicated PIVOTS and recursive CTE's to you, ok?
Lowell
May 5, 2011 at 1:04 pm
Thanks! I knew it would be something simple.
UPDATE ADDRESS
SET ADDRESS_STATE_ID = s.STATE_ID
FROM zipcode z
INNER JOIN state_ref s
ON s.STATE_ABBREV = z.STATE_CD
WHERE ADDRESS.ADDRESS_ZIPCODE_TXT = z.ZIP_CD
AND ADDRESS_STATE_ID IS NULL
AND ADDRESS_ZIPCODE_TXT IS NOT NULL
worked like a charm!
May 5, 2011 at 1:10 pm
Lowell (5/5/2011)
Sean Lange (5/5/2011)
Lowell you beat me to the punch again. 😛I'm hitting a lot of low hanging fruit today! I'll leave complicated PIVOTS and recursive CTE's to you, ok?
Sweet thanks!!! I am kind of tailing a consultant this week who is installing our new accounting package so I have lots of wait time this week.
Of course low hanging fruit is my specialty.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 5, 2011 at 1:15 pm
sqlgirlatty (5/5/2011)
Thanks! I knew it would be something simple.UPDATE ADDRESS
SET ADDRESS_STATE_ID = s.STATE_ID
FROM zipcode z
INNER JOIN state_ref s
ON s.STATE_ABBREV = z.STATE_CD
WHERE ADDRESS.ADDRESS_ZIPCODE_TXT = z.ZIP_CD
AND ADDRESS_STATE_ID IS NULL
AND ADDRESS_ZIPCODE_TXT IS NOT NULL
worked like a charm!
Excellent
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply