Cursorsgreenspun.com : LUSENET : SQL Server Database Administration : One Thread
How would you write an update cursor routine to update a column with another column based on a criteria?
for example, total is 0 but amount has a value and the itemid is sequencial. so I want to set the total to amount for each row.
would this work?
DECLARE @counter int DECLARE @columnname varchar(30) DECLARE column_cursor CURSOR FOR SELECT amount FROM table1 OPEN column_cursor FETCH NEXT FROM column_cursor INTO @columnname WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN SELECT @counter = @counter +500 EXEC ("UPDATE table1 set total = " +@columnname+" where seqid = " + @counter) END FETCH NEXT FROM column_cursor INTO @columnname END DEALLOCATE column_cursor
-- Anonymous, December 27, 1999
This particular code has not initialized @counter before referencing it. I assume you want to initialize @counter to 500.
It is also best to close the cursor before deallocating it.
Assuming that you meant to initialize @counter to 500, this code sets the total to the amount where the seqid is a multiple of 500. You could acheive the same result with this statement:
update table1 set total = amount where seqid = 500 * (seqid / 500) and seqid <> 0
Hope this helps,
-- Anonymous, January 05, 2000