Hi,

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

thanks,

-- Anonymous, December 27, 1999

Answers

Farshad,

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,

Eric

-- Anonymous, January 05, 2000