我如何在一个UPDATE语句上创建循环，该循环将一直有效直到没有行可更新为止？ [英] 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.

我计划迭代地进行更新；通过每次迭代仅更新1000行。

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.

如何运行T -SQL脚本在下面，直到没有行要更新为止？

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

推荐答案

尝试此循环

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

为什么 ISNULL -因为它不是清除-IsVegetable字段是否可为空，否则- ISNULL 不需要

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

当没有行保留IsVegetable时< > 1-循环将退出，因为@@ ROWCOUNT为= 0或< 1000（最后一次迭代）

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)

这篇关于我如何在一个UPDATE语句上创建循环，该循环将一直有效直到没有行可更新为止？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！