locked
How to update a column RRS feed

  • Question

  • Hi experts,

    I have a table that has two columns. One column has ID from 1 to 1800. The other column is null. I want to update the second column with values from 29 to 43. So for ID 1, value will be 29, ID value will 30 and it goes to 43. Then after it will start from 29 again and goes to 43. It goes all the way to the highest ID, i.e. 1800.

    I have added a script for better clarity 

    declare @t table (stuID int, valueID int)
    insert into @t (stuID, valueID)
    values (1,null), (2,null), (3,null), (4,null)
    ------the null part is what I want to have from 29 to 43 .... all the way untill the ID reachs to 1800

    Thanks much!


    ebro

    Tuesday, May 19, 2015 7:00 PM

Answers

  • Join the table with this cte and update the valueID

    ;with cte as 
    (
    select 1 as stuID, 29 as valueID
    union all 
    select stuID+1 stuID, case when valueID<43 then valueID+1 else 29 end valueID from cte where stuID<1800
    )
    select * from cte option (MAXRECURSION 1800)


    Thanks, Bharath bharath-msbi.blogspot.com

    • Marked as answer by ebrolove Tuesday, May 19, 2015 7:13 PM
    Tuesday, May 19, 2015 7:06 PM
  • UPDATE tbl
    SET    secondcol = (ID - 1) % 15 + 29


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Eric__Zhang Monday, June 1, 2015 8:59 AM
    Tuesday, May 19, 2015 9:51 PM

All replies

  • Join the table with this cte and update the valueID

    ;with cte as 
    (
    select 1 as stuID, 29 as valueID
    union all 
    select stuID+1 stuID, case when valueID<43 then valueID+1 else 29 end valueID from cte where stuID<1800
    )
    select * from cte option (MAXRECURSION 1800)


    Thanks, Bharath bharath-msbi.blogspot.com

    • Marked as answer by ebrolove Tuesday, May 19, 2015 7:13 PM
    Tuesday, May 19, 2015 7:06 PM
  • you are the man!

    Thanks much sir


    ebro

    Tuesday, May 19, 2015 7:14 PM
  • declare @t table (stuID int, valueID int)
    insert into @t (stuID, valueID)
    values (1,null), (2,null), (3,null), (4,null)
    
    
     Select row_number() Over(Order by stuID , num1 ) stuID , num1 as valueID   from @t 
     cross join (values(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43) ) d1(num1)
     

    Tuesday, May 19, 2015 7:25 PM
  • UPDATE tbl
    SET    secondcol = (ID - 1) % 15 + 29


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Eric__Zhang Monday, June 1, 2015 8:59 AM
    Tuesday, May 19, 2015 9:51 PM