Complex updates

Question

Post reply

Complex updates

BuilderBits

SSCertifiable

Points: 6071
More actions
September 12, 2009 at 10:07 am

#93948

I am trying to update my zips and citys entities and I need some help showing me how to do complex updates.
--This does not work
--------------------------------------------------------------------------------------
UPDATE Person.utbCitys
SET [TYPE] =
(SELECT z.CityType
FROM dbo.ZipCodes z
WHERE z.CityID = Person.utbCitys.CityID)
WHERE EXISTS
( SELECT z.CityID
FROM dbo.ZipCodes z
WHERE z.CityID =Person.utbCitys.CityID);
Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
--------------------------------------------------------------------------------------
Here is my new zips entity before I break it apart. Right now I am querying the data to place it in the correct entities.
CREATE TABLE [dbo].[ZipCodes](
[ZIPCode] [nvarchar](5) NOT NULL,
[ZIPType] [nvarchar](1) NOT NULL,
[CityName] [nvarchar](64) NOT NULL,
[CityType] [nvarchar](1) NOT NULL,
[CountyName] [nvarchar](255) NULL,
[CountyFIPS] [nvarchar](255) NULL,
[StateName] [nvarchar](64) NOT NULL,
[StateAbbr] [nvarchar](2) NOT NULL,
[StateFIPS] [nvarchar](255) NULL,
[MSACode] [nvarchar](255) NULL,
[AreaCode] [nvarchar](16) NOT NULL,
[TimeZone] [nvarchar](16) NULL,
[UTC] [int] NULL,
[DST] [nvarchar](255) NULL,
[Latitude] [float] NOT NULL,
[Longitude] [float] NOT NULL,
[CityID] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [Person].[utbCitys](
[CityID] [int] IDENTITY(1,1) NOT NULL,
[City] [varchar](30) NOT NULL,
[Type] [char](1) NOT NULL,
CONSTRAINT [PK_utbCitys_CityID] PRIMARY KEY CLUSTERED
(
[CityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_utbCitys] UNIQUE NONCLUSTERED
(
[CityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [Person].[utbCitys] ADD CONSTRAINT [DF_utbCitys_City] DEFAULT (' ') FOR [City]
GO
ALTER TABLE [Person].[utbCitys] ADD DEFAULT (' ') FOR [Type]
GO
Thanks for the help.
Dam again!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Following please find an untested proposal.

It should be verified using a test system and not used for production directly.

Since you didn't provide any sample data nor expected results we don't know what you're looking for...

UPDATE Person.utbCitys

SET Person.utbCitys.[TYPE] = z.CityType

FROM Person.utbCitys

INNER JOIN dbo.ZipCodes z

ON z.CityID = Person.utbCitys.CityID

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Paul White SSC Guru Points: 150457 More actions · Answer 2

Just a small something to add: I notice your script includes SET ANSI_PADDING OFF.

Books Online
In a future version of Microsoft SQL Server ANSI_PADDING will always be ON and any applications that explicitly set the option to OFF will produce an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

BuilderBits SSCertifiable Points: 6071 More actions · Answer 3

BuilderBits

SSCertifiable

Points: 6071

September 12, 2009 at 10:55 pm

#1052768

Thank you guys for all the help !

Dam again!