[dba-VB] Update without using a view

Jack,

That would, in this specific case, because in this case everything is contained in a single table.

I am trying to solve a larger problem however which is that I often have to do updates to views 
where I am joining table A to Table B On PKID and then updating tableA.FieldA = TableB.FieldA.

I am not a SQL guru and I do not play one on TV, but the only way I know how to do that is to save 
the query as a view, then update the view.  By learning how to use Common Table Expressions (CTEs), 
I can create the view "in memory" and update it right then and there.

What I used to do was create the query in code, then save the query (as a view) in code, then update 
the saved view in code.  The issue I ran into is that TSQL didn't like to save a query using the 
DBName.Dbo.Viewname syntax.  IOW the stored procedure doing all of this had to be stored in and 
executed from the database that the view would be saved in.

I am trying to create a library of stored procedures that can be run against any database and stored 
in a "library" database.

This CTE thingie provides me with that "indirection".

John W. Colby
www.ColbyConsulting.com


Jack and Pat wrote:
> 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