CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Erik Dekker
Erik Dekker

Reputation: 2433

How can I do a SQL UPDATE in batches, like an Update Top?

Is it possible to add a TOP or some sort of paging to a SQL Update statement?

I have an UPDATE query, that comes down to something like this:

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)

What is the easiest way to do this?

Upvotes: 22

Views: 62708

Answers (5)

Ani
Ani

Reputation: 11

DECLARE @updated_Rows INT;
SET @updated_Rows = 1;
WHILE (@updated_Rows > 0)
BEGIN
UPDATE top(10000) XXX SET XXX.YYY = #TempTable.ZZZ FROM XXX
INNER JOIN  #TempTable ON XXX.SomeId=#TempTable.SomeId
WHERE SomeConditions
SET @updated_Rows = @@ROWCOUNT;
END

Upvotes: 1

W. Nema
W. Nema

Reputation: 329

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

Upvotes: 4

Michał Powaga
Michał Powaga

Reputation: 23173

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.

Upvotes: 5

user806549
user806549

Reputation:

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.

Upvotes: 3

Eric Petroelje
Eric Petroelje

Reputation: 60498

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

Upvotes: 23

Related Questions