Solved

Cursor to increment value by 3 with each loop

Posted on 2006-11-17
4
2,151 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
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 50 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 50 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

786 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