none
Update the column based on for every n number of records

    Question

  • I have a temp table with two columns.One is Identity column and the another one is Increment column. which has no values.

    I have 100 rows in the temp table and in the Autoincrement column the 8th row value is 8 and from that 8th row i have to increment 10 so that i have to update the 18th row Autoincrement column value should be 18 and from that 18th row again to increment 10 so that i have to update the 28th row Autoincrement column value should be 18  and it need to repeat untill it reaches 100 rows.

    How to do this? Here is the sample data.

    ID  Increment

    8 - 8 (Already data there)  need to increment from 8th row for every 10 rows untill the end of the table.

    18 - 18 (8+10)

    28 - 28 (18+10)

    38 - 38 (28+10)

    48 - 48 (38+10)

    Friday, April 05, 2013 3:28 PM

Answers

  • declare @sample int =10
    declare @firstval int
    
    Select @firstval= Increment from tmp WHERE Increment is not null 
    
    ;with mycte as (
    
    Select *, row_number() Over(order by id) rn from tmp)
    
    
    Update mycte
    Set Increment=id
    where (rn-@firstval)% @sample=0 and AND rn>@firstval
    
    SELECT * from tmp WHERE Increment is not null

    Friday, April 05, 2013 7:29 PM
    Moderator

All replies

  • Hi -

    I am not clear on what you are asking.

    Do you simply need to set the increment value as 10 more than the ID for every row?

    In your sample data you have the ID and the Increment values the same ( 18 - 18, 28 - 28, etc).  So, it almost seems like you just want to have the Increment column value be the same as the ID for each row, but I suspect that is not what you mean.

    If you can offer some more info, that would be great

     - will


    - will

    Friday, April 05, 2013 3:51 PM
  • Here is a sample from another thread:

    declare @sample int =10
    declare @firstval int
    
    Select @firstval= Increment from tmp WHERE Increment is not null 
    
    ;with mycte as (
    
    Select *, row_number() Over(order by id) rn from tmp)
    
    
    Update mycte
    Set Increment=id
    where (rn+@firstval)% @sample=0
    
    SELECT * from tmp WHERE Increment is not null

    Friday, April 05, 2013 3:51 PM
    Moderator
  • In your sample data you have the ID and the Increment values the same ( 18 - 18, 28 - 28, etc).  So, it almost seems like you just want to have the Increment column value be the same as the ID for each row, but I suspect that is not what you mean.

    yes,that's right.

    8 - 8 (Already data there)  need to increment from 8th row for every 10 rows untill the end of the table.

    18 - 18 (8+10) need to update these rows for every 10 rows.Right now

    28 - 28 (18+10)

    38 - 38 (28+10)

    48 - 48 (38+10)

    Friday, April 05, 2013 4:29 PM
  • In your sample data you have the ID and the Increment values the same ( 18 - 18, 28 - 28, etc).  So, it almost seems like you just want to have the Increment column value be the same as the ID for each row.

    yes, I just want to have the Increment column value be the same as the ID for each row.

    Friday, April 05, 2013 4:31 PM
  • So, if you simply want the Increment column value to be the same as the ID value, why can't you just use an update query like this?

    Update Temptable
       Set Increment = ID 


    - will

    Friday, April 05, 2013 4:46 PM
  • >> I have a temp table with two columns. One is IDENTITY column and the another one is Increment column, which has no values. <<

    No; IDENTITY is not a column by definition; it is a table property. There is no such attribute as a generic, magical “id” in RDBMS. It has to be the identifier of something in particular. We also do not like to use temp tables. That is how non-SQL programmers fake scratch tapes or files so they can write procedural code with loops, if-then control flow, recursion, etc. 

    >> I have 100 rows in the temp table and in the Autoincrement column the 8th row value is 8 and from that 8th row I have to increment 10 so that I have to update the 18th row Autoincrement column value should be 18 and from that 18th row again to increment 10 so that I have to update the 28th row Autoincrement column value should be 18 and it need to repeat until it reaches 100 rows. <<

    This is not how we think in SQL. You have just described a counting loop in procedural code. SQL is a set-oriented declarative language. We just create a table and load it. A spreadsheet is a good way. But you have described a formula that you can write in an expression rather than use a table.  



    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, April 05, 2013 5:42 PM
  • declare @sample int =10
    declare @firstval int
    
    Select @firstval= Increment from tmp WHERE Increment is not null 
    
    ;with mycte as (
    
    Select *, row_number() Over(order by id) rn from tmp)
    
    
    Update mycte
    Set Increment=id
    where (rn-@firstval)% @sample=0 and AND rn>@firstval
    
    SELECT * from tmp WHERE Increment is not null

    Friday, April 05, 2013 7:29 PM
    Moderator