Hi,

If I have readings in a table and want to divide the values by some number, is it true that iterating through the range ( which can be 1 day, 1 week, 1 month, 6 months.. anything) and using the update command for each individual row is a better idea than a bulk update for the whole range? Would it put less of a 'burden' on the server if I go the individual update route?

here is an example of the command --
update <tablename> set readingval = readingval / <number> where .....

thanks!!

The problems with doing something like this in its entirety is the locking and logging. Can your application handle not being to access a large block of pages in this table (depending on the range you are updating) without your users crying foul? Are your logs large enough to handle all of the logging (again, depending on the size of the range you are dealing with)?
By running a cursor, then updating each row returned, you can add commit after n updates and restart logic. By doing this you are allowing other users access to the information you are updating either after/before you get to it. The restart logic would allow you to pick back up where you left off if the process were to abort or you had to cancel it for some reason.
Dave

thanks for the response.. I forgot to mention that this is being done in a stored procedure and the table that is being accessed is a global temp table. I think the cursor method with commit is probably the best way too.. how can I check if the logs are large enough to handle the logging? will something like this work --

--update statement
SET updateStatement = 'UPDATE <table> SET val = val / ? WHERE Id = ? AND
datetime > ? and datetime <= ?';


PREPARE s1 FROM updateStatement;

WHILE (startInterval <= endTime) DO
BEGIN
EXECUTE s1 USING param_val, param_id, param_start, param_end;

SET startInterval = --- new start interval
SET endInterval = ---- new end interval

<<ADD COMMIT HERE >>
END;
END WHILE;

If it is a temp table, you don't have to care about logging because operations on such tables are not logged.