SQL CURSOR Help

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
salesprodAsked:
Who is Participating?
 
derekkrommConnect With a Mentor Commented:
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
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
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.