The usual way to
write the update method is as shown below:
UPDATE
test
SET col=0
WHERE
col<0
The issue with this
query is that it will take a lot of time as it affects 2 million rows and also
locks the table during the update.
You can improve the
performance of an update operation by updating the table in smaller groups.
Consider the following code:
SET rowcount 10000
UPDATE test
SET col=0
WHERE
col<0
WHILE @@rowcount>0
BEGIN
SET rowcount 10000
UPDATE test
SET col=0
WHERE
col<0
END
SET rowcount 0
The above code
updates 10000 rows at a time and the loop continues till @@rowcount has a value
greater than zero. This ensures that the table is not locked.
