November 3, 2009 at 5:40 pm
Hi,
This may seem a little strange. But here is my challenge. I have data in Table 1. I execute 4 different insert Stored Procedures that summarize data from Table 1 and insert it into Table 2. Once this is complete. I need to then go and update a column in Table 2 for the newley insterted rows. The update needs to increment by 500 for each newly added row in Table 2.
For example, when executing the 4 SP that insert data into table 2 from table 1, six new rows are added to table 2. I need to do the following
ROW 1, 500
ROW 2, 1000
ROW 3, 1500
ROW 4, 2000
ROW 5, 2500
ROW 6, 3000
Thanks
November 3, 2009 at 11:09 pm
Hi
use the following query.
with cte(col1,col2,rownum) as (
select col1,col2,row_number() over(order by col)
from table2
where <condition >
)
update t2
set column1=rownum*500
from table2 t2 inner join cte
on <join conditions>
where <condition>
in the CTE select the keys of the table table2 that should participate in the join in the update.order the row_number function by the column that You use to order when you row1,row2,row3 etc.resultset of the query would be
col1,col2,1
col1,col2,2
col1,col2,3
and so on.
when u update u multiply the rownum value (1,2,3) with 500 to the desired result.
November 4, 2009 at 5:47 am
Thank you for your reply!!! I seem to have a problem though. Below is the script I am using with 5 rows affected. What is happening is I am getting the follwoiugn results:
500
2500
500
2500
500
Here is the script
with cte(Transfer_SID,Batch_Ref_Num,rownum) as (
select Transfer_SID,Batch_Ref_Num,row_number() over(order by Transfer_SID)
from tbl_Step3_TranBatch
where Transfer_SID=69 AND Batch_ref_Num='xx45th'
)
update t2
Set t2.SQCLINE = rownum*500
from tbl_Step3_TranBatch t2 inner join cte on t2.Transfer_SID=cte.Transfer_SID
where t2.Transfer_SID=69 AND t2.Batch_ref_Num='xx45th'
November 4, 2009 at 6:01 am
Hi....After a little research on CTE i have figured it out!!!! THANK YOU SOOO MUCH
November 5, 2009 at 9:10 am
Please post your solution so that others may benefit.
Julie
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply