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

Question

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 1

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