How can I create a loop on an UPDATE statement that works until there is no row left to update?

Question

Assume that I have thousands of rows to update.

And I plan to do the update iteratively; by only updating 1000 rows per iteration.

And I want to iterate until there are no rows left to update.

How can I run the T-SQL script below until there is no row to update?

-- TODO: Create a loop so that it exists when there is no ROW left to be updated; 
-- how can I do it?

UPDATE tableToUpdate
SET IsVegetable = 1
WHERE Id IN
               (SELECT TOP 1000 Id
                FROM tableToUpdate
                WHERE Date = '2011-07-23 14:00')

-- Loop ends

Doud

Why do you wanna update only 1000 max each time? To me it seems that removing the WHERE clause would solve your problem. But you specificly say you want to do it 1000 a time im just wondering why? – Nevis

I gave the numbers as an example but the reason is to prevent transaction time-outs. And secondary reason is : Curiosity on the implementation. – Doud

Answer 1

Try this loop

while 1 = 1
BEGIN
    UPDATE top (1000) tableToUpdate
    SET IsVegetable = 1
    WHERE 
        Date = '2011-07-23 14:00'
    AND IsNull(IsVegetable, 0) = 0

    if @@ROWCOUNT < 1000 BREAK
END

Why ISNULL - because it is not clear - if the field IsVegetable is nullable or not, if not - then ISNULL not needed

When there no rows will left with IsVegetable <> 1 - the loop will quit because the @@ROWCOUNT will be = 0 or < 1000 (for the last iteration)

Marquess

Can you please explain, why did you need this: AND IsNull(IsVegetable, 0) = 0 – Doud

And can you explain the mechanism that ensures the loop will work until 0 rows left? – Doud

@Oleg: Thank you Oleg! So it seems I cannot reach the @@ROWCOUNT value from the WHILE statement? – Doud