6

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
pencilCake
  • 51,323
  • 85
  • 226
  • 363
  • 1
    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? – Ruben Dec 29 '11 at 09:31
  • 1
    I gave the numbers as an example but the reason is to prevent transaction time-outs. And secondary reason is : Curiosity on the implementation. – pencilCake Dec 29 '11 at 09:33

1 Answers1

10

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)

Oleg Dok
  • 21,109
  • 4
  • 45
  • 54