How can I do a SQL UPDATE in batches, like an Update Top?
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:
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
If this is to be done repeatedly, you can set an index on the BATCHID and reduce load on the server.
Yes, I believe you can use TOP in an update statement, like so:
UPDATE TOP (10000) XXX SET XXX.YYY = #TempTable.ZZZ
FROM XXX
INNER JOIN (SELECT SomeFields ... ) #TempTable ON XXX.SomeId=#TempTable.SomeId
WHERE SomeConditions
You can use SET ROWCOUNT { number | @number_var }
it limits number of rows processed before stopping the specific query, example below:
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.ZZZ
to where
clause to make sure you will not update twice already updated value.
Setting ROWCOUNT
to 0
turn off limits - don't forget about it.
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