Solved

update within CTE

Posted on 2014-01-09
11
256 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

785 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