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
If you do not use where clause in UPDATE statement, all the records in the table will be updated with the specified values.
Or, to repharse what I've just said - there isn't a for loop in SQL. WHILE @I < 10; SET @I = @I + 1; BEGIN; ...; END ? However, this should not be used for most query processing (but is sometimes required for imperative manipulation).
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With