Erik Dekker Erik Dekker - 3 months ago 28
SQL Question

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?

Answer

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