update within CTE

;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.
LVL 9
Seven priceFull StackAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CrashmanCommented:
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
Seven priceFull StackAuthor Commented:
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.
Seven priceFull StackAuthor Commented:
oops it was a mistake
not ,Table5.row4=1 --I don't like this line.

Table2.row1
would be updated
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

CrashmanCommented:
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?
Seven priceFull StackAuthor Commented:
thats correct. Table 5 is just another table that has the data and updates table1.
Seven priceFull StackAuthor Commented:
;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.
Seven priceFull StackAuthor Commented:
the only relationship between table1 and table5 is and ID but there is not relationship with Table2 and table5.
CrashmanCommented:
you are trying to update table1 with table5 data, what is the relations between this two.
Seven priceFull StackAuthor Commented:
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.
CrashmanCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Seven priceFull StackAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.