Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Cursor to increment value by 3 with each loop

Posted on 2006-11-17
4
Medium Priority
?
2,279 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:kpu8
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 17

Expert Comment

by:HuyBD
ID: 17970055
kpu8, why dont you try one query without cursor

update acct_data set balance=balance +3
where balance in (1000,1003)
0
 
LVL 29

Accepted Solution

by:
Gautham Janardhan earned 200 total points
ID: 17970359
why dont u try this

DECLARE @Y INT
SElect @Y = balance from tablebal

UPDATE tablebal SET @Y = balance =  (@Y + 3)
0
 
LVL 28

Assisted Solution

by:imran_fast
imran_fast earned 200 total points
ID: 17970805
-- 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
 
LVL 1

Author Comment

by:kpu8
ID: 17973085
Both worked thanks!

0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question