Incrementing row values in a loop RSS

• • Reply
  

  AppDevForMe

  Participant

  1392 Points

  1324 Posts

  Incrementing row values in a loop

  Jun 11, 2009 04:09 AM|LINK

  I have a database table very similar to the example
  below. Basically you have a name, and a unique numerical
  ID next to it. (Not an indexed or primary key)

  I need to write a stored procedure that will be able to
  increment the Id by 1 for a particular name, and value.

  tblSample:

  Name        Id
  Jeff Boker  0
  Jeff Boker  1 
  Jeff Boker  2
  Jeff Boker  3
  Jeff Boker  4
  Jeff Boker  5
  Jeff Boker  6

  
  So in this case I would do something like this:
  loop
  update tblSample set Id = Id + 1 where Id = @value
  @value=Value+1
  end loop

  Where @value represents the Id I want to start incrementing at.
  So for example if I pass in the value 3, the Id column will start
  to increment by 1 starting at row 3. So the table would then look
  like this after the incrementing:

  Name        Id
  Jeff Boker  0
  Jeff Boker  1 
  Jeff Boker  2
  Jeff Boker  4
  Jeff Boker  5
  Jeff Boker  6
  Jeff Boker  7

  Can someone show me how to do this? The problem with my pseudo code is that
  It would just continue to increment the same row over an over.

   

• • Reply
  

  mo meng

  Contributor

  6700 Points

  1351 Posts

  Re: Incrementing row values in a loop

  Jun 11, 2009 04:16 AM|LINK

  update tblSample set Id = Id + 1 where Id >= @value

  no need loop

  • Marked as answer by AppDevForMe on Jun 11, 2009 03:08 PM