Update Rows by Increment of 500

  • 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

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

  • 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'

  • Hi....After a little research on CTE i have figured it out!!!! THANK YOU SOOO MUCH

  • 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