Solved

update within CTE

Posted on 2014-01-09
11
258 Views
Last Modified: 2014-01-10
;WITH CTE1
AS
(
Select Table1.row1, Table1.row2, Table1.row3, Table2.row1
from Table1
left Join table2 on table2.Id = table1.Id
)
,CTE2
AS
(
Select Table5.row1, Table5.row2,Table5.row3,Table5.row4
from Table5

)

Update
CTE1
set Table1.row1=Table5.row1
,Table1.row2=Table5.row2
,Table1.row3=Table5.row3
,Table5.row4=1
FROM
    CTE2

Open in new window

Trying to update CTE2 Table5 after CTE1 Table1 is updated. Gives error.
0
Comment
Question by:sevensnake77
  • 7
  • 4
11 Comments
 
LVL 8

Expert Comment

by:Crashman
ID: 39769560
Whats is your real goal. what are you trying to do?



Update
CTE1
set Table1.row1=Table5.row1
,Table1.row2=Table5.row2
,Table1.row3=Table5.row3
,Table5.row4=1 --I don't like this line.
FROM
    CTE2
0
 
LVL 9

Author Comment

by:sevensnake77
ID: 39769567
Once each Table1 row has been updated then I want to update Table5.row4 to 1, it is like a flag saying which row has been updated.
0
 
LVL 9

Author Comment

by:sevensnake77
ID: 39769570
oops it was a mistake
not ,Table5.row4=1 --I don't like this line.

Table2.row1
would be updated
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 8

Expert Comment

by:Crashman
ID: 39769579
You want update table 1, which has relations with table 2, with data of table 5 , is correct ?
can you post an example of your data, exist relations between tabla 1 and table 5?
0
 
LVL 9

Author Comment

by:sevensnake77
ID: 39769589
thats correct. Table 5 is just another table that has the data and updates table1.
0
 
LVL 9

Author Comment

by:sevensnake77
ID: 39769593
;WITH CTE1
AS
(
Select Table1.row1, Table1.row2, Table1.row3, Table2.row1
from Table1
left Join table2 on table2.Id = table1.Id
)
,CTE2
AS
(
Select Table5.row1, Table5.row2,Table5.row3,Table5.row4
from Table5

)

Update
CTE1
set Table1.row1=Table5.row1
,Table1.row2=Table5.row2
,Table1.row3=Table5.row3
,Table2.row1= 1
FROM
    CTE2

Open in new window


so this is the correct way.
0
 
LVL 9

Author Comment

by:sevensnake77
ID: 39769627
the only relationship between table1 and table5 is and ID but there is not relationship with Table2 and table5.
0
 
LVL 8

Expert Comment

by:Crashman
ID: 39769644
you are trying to update table1 with table5 data, what is the relations between this two.
0
 
LVL 9

Author Comment

by:sevensnake77
ID: 39769654
I am not trying to update table5. I am using table 5 to update table1. Table5 is where I am pulling the data from to insert into table1.

basically when table5 lets say UPC code matches Table1 upc code then update.
0
 
LVL 8

Accepted Solution

by:
Crashman earned 500 total points
ID: 39769677
you can't update two tables in one statement

try this.


Update table1 with table5

update x
set x.row1 = y.row1 ..and so on
from table1 as x inner join table5 as y on x.ID5 = y.ID5

update table2

update x
set x.row1 = 1
from table2 as x inner join table1 as y on y.ID1 = x.ID1
inner join table5 as z on z.ID5 = y.ID5

Open in new window

0
 
LVL 9

Author Closing Comment

by:sevensnake77
ID: 39771509
That did  not work for me either but  what I did is create another stored procedure and execute it after this one ran. Thanks again.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question