How to chunk updates to SQL Server? -

i want update table in sql server setting flag column 1 values since beginning of year:

table date        id     flag   (more columns...) 2016/01/01  1      0      ... 2016/01/01  2      0      ... 2016/01/02  3      0      ... 2016/01/02  4      0      ... (etc) 

problem table contains hundreds of millions of records , i've been advised chunk updates 100,000 rows @ time avoid blocking other processes.

i need remember rows update because there background processes flip flag 0 once they're done processing it.

does have suggestions on how can this? each day's worth of data has on million records, can't loop using date counter. thinking of using id

assuming date column , id column sequential simple loop. mean if there record id=1 , date=2016-1-1 record id=2 date=2015-12-31 not exist. if worried locks/exceptions should add transaction in while block , commit or rollback on failure.

change @batchsize whatever feel right after experimentation.

declare @currentid int, @maxid int, @batchsize int = 10000  select @currentid = min(id), @maxid = max(id) yourtable date >= '2016-01-01'  while @currentid < @maxid begin     update yourtable set flag = 1 id between @currentid , (@currentid + @batchsize)     set @currentid = @currentid + @batchsize end 

as update never flag same record 1 twice not see need track records touched unless going manually stop process partway through.

you should ensure id column has index on retrieval fast in each update statement.