[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

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

Table2.row1
would be updated
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now