Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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
like image 204
pencilCake Avatar asked Dec 29 '11 09:12

pencilCake


People also ask

When using the update statement if you do not specify the WHERE clause all the rows in the table are updated?

If you do not use where clause in UPDATE statement, all the records in the table will be updated with the specified values.

Can you do a for loop in a SQL query?

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).


1 Answers

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)

like image 129
Oleg Dok Avatar answered Oct 21 '22 06:10

Oleg Dok