[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
> 
>