Cursor for updating a row with values ​​from the previous and current rows

Question

Screenwriters

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 every zero on col3 with the col3 value in the previous row plus the col2 value in the current row. So my table will look like this:

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

I miss the logic here, perhaps myopia. I tried using the 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 this is wrong. Any ideas?

Thanks in advance.

Answer 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 the cursor in most cases.

Answer 2

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

 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 

He solved my problem. Thanks to everyone.

Answer 3

Here is one UPDATE statement that uses common table expressions (CTE) to update 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 ; 

Answer 4

FWIW The main, convincing argument for using CURSOR is that when you do not, it will suffer too much from your rdbms. You can almost always use a WHILE loop instead of a CURSOR; processing ONE record at a time; can be very useful when for some reason you may need to repeat a large number of records ... CURSOR operations are exponentially more efficient than the equivalent SET operation.

All in all, it comes down to speed and performance improvements ...

CURSORS are the slowest path, but have the least amount of overhead and are still useful even in MSSQL 2012 ...