如何批量执行 SQL UPDATE，例如更新顶部？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8820553/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I do a SQL UPDATE in batches, like an Update Top?
提问by Erik Dekker
Is it possible to add a TOP or some sort of paging to a SQL Update statement?
是否可以向 SQL Update 语句添加 TOP 或某种分页？
I have an UPDATEquery, that comes down to something like this:
我有一个UPDATE查询，归结为这样的：
UPDATE XXX SET XXX.YYY = #TempTable.ZZZ
FROM XXX
INNER JOIN (SELECT SomeFields ... ) #TempTable ON XXX.SomeId=#TempTable.SomeId
WHERE SomeConditions
This update will affect millions of records, and I need to do it in batches. Like 100.000 at the time (the ordering doesn't matter)
这次更新会影响几百万条记录，我需要分批进行。当时像 100.000（顺序无关紧要）
What is the easiest way to do this?
什么是最简单的方法来做到这一点？
回答by Eric Petroelje
Yes, I believe you can use TOP in an update statement, like so:
是的，我相信您可以在更新语句中使用 TOP，如下所示：
UPDATE TOP (10000) XXX SET XXX.YYY = #TempTable.ZZZ
FROM XXX
INNER JOIN (SELECT SomeFields ... ) #TempTable ON XXX.SomeId=#TempTable.SomeId
WHERE SomeConditions
回答by Micha? Powaga
You can use SET ROWCOUNT { number | @number_var }it limits number of rows processed before stopping the specific query, example below:
您可以SET ROWCOUNT { number | @number_var }在停止特定查询之前使用它限制处理的行数，示例如下：
SET ROWCOUNT 10000 -- define maximum updated rows at once
UPDATE XXX SET
XXX.YYY = #TempTable.ZZZ
FROM XXX
INNER JOIN (SELECT SomeFields ... ) #TempTable ON XXX.SomeId = #TempTable.SomeId
WHERE XXX.YYY <> #TempTable.ZZZ and OtherConditions
-- don't forget about bellow
-- after everything is updated
SET ROWCOUNT 0
I've added XXX.YYY <> #TempTable.ZZZto whereclause to make sure you will not update twice already updated value.
我添加了XXX.YYY <> #TempTable.ZZZtowhere子句以确保您不会更新两次已经更新的值。
Setting ROWCOUNTto 0turn off limits - don't forget about it.
设置ROWCOUNT到0关闭的限制-不要忘记它。
回答by W. Nema
You can do something like the following
您可以执行以下操作
declare @i int = 1
while @i <= 10 begin
UPDATE top (10) percent
masterTable set colToUpdate = lt.valCol
from masterTable as mt
inner join lookupTable as lt
on mt.colKey = lt.colKey
where colToUpdate is null
print @i
set @i += 1
end
--one final update without TOP (assuming lookupTable.valCol is mostly not null)
UPDATE --top (10) percent
masterTable set colToUpdate = lt.valCol
from masterTable as mt
inner join lookupTable as lt
on mt.colKey = lt.colKey
where colToUpdate is null
回答by W. Nema
Depending on your ability to change the datastructure of the table, I would suggest that you add a field to your table that can hold some sort of batch-identificator. Ie. it can be a date-stamp if you do it daily, an incremenal value or basically any value that you can make unique for your batch. If you take the incremental approach, your update will then be:
根据您更改表数据结构的能力，我建议您向表中添加一个字段，该字段可以包含某种批标识符。IE。如果您每天都这样做，它可以是一个日期戳、一个增量值或基本上任何可以为您的批次设置的唯一值。如果您采用增量方法，那么您的更新将是：
UPDATE TOP (100000) XXX SET XXX.BATCHID = 1, XXX.YYY = ....
...
WHERE XXX.BATCHID < 1
AND (rest of WHERE-clause here).
Next time, you'll set the BATCHID = 2 and WHERE XXX.BATCHID < 2
下次，您将设置 BATCHID = 2 和 WHERE XXX.BATCHID < 2
If this is to be done repeatedly, you can set an index on the BATCHID and reduce load on the server.
如果要重复执行此操作，可以在 BATCHID 上设置索引并减少服务器负载。
