?
Solved

SQL CURSOR Help

Posted on 2007-08-01
2
Medium Priority
?
552 Views
Last Modified: 2008-02-24
Hi.  I am trying to create my first cursor that goes through a table row by row calculating a column called MM_CUM.  I keep track of the previous row's r1_mmestwireless value as it is used in the next row's calculation for MM_CUM.  I try this and it goes through all 15,000 rows and places the same value in the MM_CUM column.  Any help with this would be much appreciated.  Thank you!


DECLARE @iRowId int,
      @r1_mmestwireless money,
      @last_mm_cum money,
      @mm_cum money

DECLARE MarketModel CURSOR LOCAL
FORWARD_ONLY
FOR
SELECT r1_mmestwireless,mm_cum
FROM mm_mal_test

SET @iRowID = 1

OPEN MarketModel

FETCH NEXT FROM MarketModel
INTO @r1_mmestwireless, @mm_cum
WHILE @@FETCH_STATUS = 0

BEGIN

IF (@iRowID = 1)
BEGIN
UPDATE MM_MAL_TEST
SET mm_cum = @r1_mmestwireless
SET @last_mm_cum = @r1_mmestwireless
END

IF (@iRowID > 1)
BEGIN
UPDATE MM_MAL_TEST
SET mm_cum = @last_mm_cum + @r1_mmestwireless
SET @last_mm_cum = @last_mm_cum + @r1_mmestwireless
END

FETCH NEXT FROM MarketModel
INTO @r1_mmestwireless, @mm_cum

set @iRowID = @iRowID + 1

END

CLOSE MarketModel
DEALLOCATE MarketModel
RETURN
0
Comment
Question by:salesprod
2 Comments
 
LVL 15

Accepted Solution

by:
derekkromm earned 1000 total points
ID: 19611753
Your update statement affects the entire table. Be sure to FETCH the primary key of the table and add it to the where clause of your UPDATE:

UPDATE MM_MAL_TEST
SET mm_cum = @r1_mmestwireless
WHERE pk = @pk
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 total points
ID: 19611810
True.  Or, just use an updateable cursor and specify:

UPDATE ...
SET ...
WHERE CURRENT OF MarketModel

At any rate, you also need an ORDER BY statement on the original SELECT that creates the cursor.  Otherwise the rows are in random order and the "cumulative" total isn't based on any sequence anyway and could change from one run to the next with exactly the same rows in the table.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

In this article, we’ll look at how to deploy ProxySQL.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

850 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