Jack and Pat
drawbridgej at sympatico.ca
Fri Mar 5 15:30:34 CST 2010
John,
Glad you solved it.
I'm curious, and I don't use Sql server, but why wouldn't this work
UPDATE dbo.AZSMKRevalidate SET zip5 = Left([zip9],5), zip4 =
Right([zip9],4);
jack
-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, March 05, 2010 3:39 PM
To: Discussion concerning Visual Basic and related programming issues.;
Sqlserver-Dba
Subject: Re: [dba-VB] Update without using a view
And of course I figured it out.
WITH T1 AS
(SELECT LEFT(Zip9, 5) AS SplitZip5, RIGHT(Zip9, 4) AS SplitZip4, Zip5, Zip4
FROM dbo.AZSMKRevalidate)
UPDATE T1 SET [Zip5] = [SplitZip5]
,[Zip4] = [SplitZip4]
GO
Sorry for the ring.
John W. Colby
www.ColbyConsulting.com
jwcolby wrote:
> Guys,
>
> Sorry the previous email got mangled in the creation.
>
> I have a recurring situation where I will get a zip9 and need to split it
into zip5 / zip4. I add
> the two fields to the table, then use right() and left() to carve out the
pieces in a query as follows:
>
> SELECT LEFT(Zip9, 5) AS SplitZip5, RIGHT(Zip9, 4) AS SplitZip4, Zip5,
Zip4
> FROM dbo.AZSMKRevalidate
>
> That works great.
>
> Now if I save that to a view vSplitzip and use the following:
>
> UPDATE vSplitZip
> SET [Zip5] = [SplitZip5]
> ,[Zip4] = [SplitZip4]>
>
> the update happens and all is well.
>
> My problem is that I really want to not have to create the view, but
rather to do something like:
>
> UPDATE
> (SELECT LEFT(Zip9, 5) AS SplitZip5, RIGHT(Zip9, 4) AS SplitZip4, Zip5,
Zip4
> FROM dbo.AZSMKRevalidate)
>
> SET [Zip5] = [SplitZip5]
> ,[Zip4] = [SplitZip4]
>
> IOW to replace the view with the exact same SQL statement as is stored in
the view.
>
> When I do that however SQL Server pukes, with the infamous "error near (".
Obviously I need the
> changes to post back down to the original table that I created the new
fields on.
>
> I want to do this because if I can do it this way, then I can throw
everything into a stored
> procedure where I pass in the name of the db and table, have the SP create
the new fields in the
> table, build the SQL statement on the fly, and do the split on any db /
table.
>
> So is it possible to do an update to a select statement like this? I have
seen " AS T1" kind of
> thing but tried that and couldn't get that to work either.
>
> Any assistance greatly appreciated.
>
>
> John W. Colby
> www.ColbyConsulting.com
>
>
> jwcolby wrote:
>> Guys,
>>
>> I have a recurring situation where I will get a zip9 and need to split it
into zip5 / zip4. I add
>> the two fields to the table, then use right() and left() to carve out the
pieces in a query as follows:
>>
>> SELECT LEFT(Zip9, 5) AS SplitZip5, RIGHT(Zip9, 4) AS SplitZip4, Zip5,
Zip4
>> FROM dbo.AZSMKRevalidate
>>
>> That works great.
>>
>> Now if I save that to a view vSplitzip and use the following:
>>
>> UPDATE vSplitZip
>> SET [Zip5] = [SplitZip5]
>> ,[Zip4] = [SplitZip4] >
>
>> the update happens and all is well.
>>
>> My problem is that I really want to not have to create the view, but
rather to do something like:
>>
>> UPDATE
>> (SELECT LEFT(Zip9, 5) AS SplitZip5, RIGHT(Zip9, 4) AS SplitZip4,
Zip5, Zip4
>> FROM dbo.AZSMKRevalidate)
>>
>> SET [Zip5] = [SplitZip5]
>> ,[Zip4] = [SplitZip4]
>>
>> IOW to replace the view with the exact same SQL statement as is stored in
the view.
>>
>> When I do that however SQL Server pukes, with the infamous "error near
(". Obviously I need the
>> changes to post back down to the original table that I created the new
fields on.
>>
>> I want to do this because if I can do it this way, then I can throw
everything into a stored
>> procedure where I pass in the name of the db and table, have the SP
create the new fields in the
>> table, build the SQL statement on the fly, and do the split on any db /
table.
>>
>> So is it possible to do an update to a select statement like this? I
have seen " AS T1" kind of
>> thing but tried that and couldn't get that to work either.
>>
>> Any assistance greatly appreciated.
>>
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
>
>
_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com
No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.733 / Virus Database: 271.1.1/2723 - Release Date: 03/05/10
02:34:00