Solved

update within CTE

Posted on 2014-01-09
11
262 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
convert varchar UTC to human datetime 1 41
Copy data to New Year 9 31
Convert VBA UDF to SQl SERVER UDF 4 48
Linked Server - SP with Param to VIew 7 21
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
I have a large data set and a SSIS package. How can I load this file in multi threading?
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

756 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