cursor to update a row with values from the previo

问题:

Fellow Query Writers,

I have a table as follows:

myTable t1
col2 col3
 2    1
 3    0
 4    0
 5    0
 6    0

and I want to update each zero on col3 with the value of col3 in the previous row plus the value of col2 in the current row. So my table would de like the following:

myTable t1
col2 col3
 2    1 
 3    4  (1+3)
 4    8  (4+4)
 5    13 (5+8) 
 6    19 (6+13)

I'm missing the logic here, short-sightedness perhaps. I was trying it with a cursor as follows:

DECLARE @var3 FLOAT

DECLARE cursor3 CURSOR FOR
SELECT col2, col3 FROM table1
FOR UPDATE OF col3
OPEN cursor3


FETCH FIRST FROM cursor3
WHILE (@@FETCH_STATUS > -1)
BEGIN
 UPDATE @table1
 SET col3 = isnull(@var3, 0) + isnull(col2, 0)
 WHERE CURRENT OF cursor3
 FETCH NEXT FROM cursor3 INTO @var3
END

but it's wrong. Any ideas?

Thanks in advance.

回答1:

OK, Try this.

CREATE TABLE MyTable (Id INT Identity, Col2 int, Col3 int)

INSERT INTO MyTable (Col2, Col3)
VALUES (2,1), (3,0), (4,0),(5,0),(6,0)

SELECT * from MyTable

WHILE (SELECT COUNT(*) FROM MyTable WHERE Col3=0) > 0
BEGIN
    UPDATE TOP (1) MyTable
    SET CoL3 = (Mytable.col2 + (select col3 from mytable t2 where (t2.id = mytable.id-1)))
    WHERE Col3 = 0
END

SELECT * from MyTable

Uses a WHILE loop which should be faster than a cursor under most circumstances.

回答2:

I added an identity column to my table and ended up using a code like this:

DECLARE @saldo_Q_previous FLOAT
DECLARE @ID INTEGER

DECLARE cursor3 CURSOR FOR
SELECT ID FROM @myTable
FOR UPDATE OF col2
OPEN cursor3

FETCH NEXT FROM cursor3 INTO @ID
FETCH NEXT FROM cursor3 INTO @ID

WHILE (@@FETCH_STATUS > -1)
BEGIN

    SET @col2_previous = ISNULL((SELECT TOP 1 col2 FROM @myTable WHERE ID < @ID ORDER BY ID DESC), 0)
    SET @vrQ = ISNULL((SELECT TOP 1 vr_Q FROM @myTable WHERE ID < @ID ORDER BY ID DESC), 0)

    UPDATE @myTable
    SET col2 = isnull(@col2_previous, 0) + isnull(vrMov_Q, 0)
    WHERE CURRENT OF cursor3

    FETCH NEXT FROM cursor3 INTO @ID
END

CLOSE cursor3
DEALLOCATE cursor3

It solved my problem. Thank you all.

回答3:

Here is a single UPDATE statement that uses common table expressions (CTE) to update the data.

WITH myTable2 AS
    (
    SELECT col2, col3, ROW_NUMBER() OVER (ORDER BY col2) AS sequence
    FROM myTable
    ),
  newTable AS
    (
    SELECT t1.col2, SUM(t2.col2) - SUM(t2.col3) AS col3
    FROM myTable2 t1
    LEFT OUTER JOIN myTable2 t2 ON t1.sequence >= t2.sequence
    GROUP BY t1.col2
    )

UPDATE myTable
SET col3 = newTable.col3
FROM myTable
JOIN newTable on myTable.col2 = newTable.col2
;

回答4:

FWIW The major, compelling reason to use a CURSOR is when not doing so, will put too much of a hit on your rdbms. You can almost always use a WHILE loop instead of a CURSOR; processing ONE record at a time; can be quite useful when for whatever reason you may need to iterate a large number of records... CURSOR operations are exponentially more efficient than the equivalent SET operation.

So in general it comes down to speed & overhead vs. efficiency...

CURSORS are pretty much the slowest way to go, but have the least amount of overhead and are still useful even in MSSQL 2012 ...