Cursor to increment value by 3 with each loop

I'm using the following simple cursor in SQL Server 2005 and
I want it to add 3 to each value that get's inserted with each loop:

so if I start the first value of the balance at 1000 - the second time
through the loop - I want the balance to be 1003
the next time through I want the balance to be 1006

What happens with this cursor is it loops thourgh and makes all the balance rows equal to 1003


declare @balance varchar(15)
declare loopcurs cursor for
  SELECT balance +3 from acct_data
  for read only
open loopcurs
fetch next from loopcurs into @balance
while (@@fetch_status = 0)
begin
  Update acct_data
  set balance = @balance  
  where balance = '1000'
  fetch next from loopcurs into @balance
end /* while (@@fetch_status = 0) */
close loopcurs
deallocate loopcurs
LVL 1
kpu8Asked:
Who is Participating?
 
Gautham JanardhanConnect With a Mentor Commented:
why dont u try this

DECLARE @Y INT
SElect @Y = balance from tablebal

UPDATE tablebal SET @Y = balance =  (@Y + 3)
0
 
HuyBDCommented:
kpu8, why dont you try one query without cursor

update acct_data set balance=balance +3
where balance in (1000,1003)
0
 
imran_fastConnect With a Mentor Commented:
-- Hi,
-- this will mess up the data so you should better use the primary key of the table in the cursor
declare @PK_COL varchar(15), @Balance varchar(15),
      @Bal int
declare loopcurs cursor for
  SELECT primarykey, Balance  from acct_data
  for read only
open loopcurs
fetch next from loopcurs into @PK_COL,@balance
set @Bal = @Balance
while (@@fetch_status = 0)
begin
  set @Bal = @bal + 3
  Update acct_data
  set balance = @balance  
  where balance = @Balance  and Primarykeycolumn = @Pk_col
  fetch next from loopcurs into @PK_COL,@balance
end /* while (@@fetch_status = 0) */
close loopcurs
deallocate loopcurs
0
 
kpu8Author Commented:
Both worked thanks!

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.