Update issues with subqueries
-
I'm trying to update a field ( SFZipIndexCode ) in a table but keep getting error messages about
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
---QUERY
update
CSZipCode
set
SFZipIndexCode = (select SFMSAindexcode from CSCounty
join
cszipcode on cszipcode.countyname = CSCounty.countyname and cszipcode.state = CSCounty.state)
where
cszipcode.SFZipIndexCode = ''
--This table holds the data I need to update the other table with
CREATE
TABLE [dbo].[SFZipindexcode](
[IndexCode] [nvarchar]
(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[countyname] [nvarchar]
(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
--example of data -
ALM_030 Autauga
This is the table I need to update (SFZIPIndexCode aka Indexcode in other table)
CREATE TABLE [dbo].[CSZipCode](
[ZIPCode] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CountyName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SFZIPIndexCode] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ZIPStatus] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ZIPType] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
Thanks in advance for any help
Susan
-
You didn't include the table def for the "CSCounty" table But this should do the trick
UPDATE CSZ
SET SFZipIndexCode = SFMSAindexcode
FROM CSZipCode CSZ
INNER JOIN CSCounty CS
ON CSZ.countyName = CS.countyName
AND CSZ.state = CS.state
WHERE IsNull(CSZ.SFZipIndexCode, '') = ''
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
Awesome!!!
Worked great, thanks for your help
Susan
-
Happy to help.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply