[dba-VB] Update without using a view

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