declare @Test table (ID int Identity, BatchID char(2), Quantity int) insert into @Test values ('B1', 100) insert into @Test values ('B1', 200) insert into @Test values ('B1', 300) insert into @Test values ('B1', 500) insert into @Test values ('B2', 500) declare @Amount int set @Amount = 500 ;with Cte_RunningTotal (ID, BatchID, Quantity, Total) as (select *, (select SUM(Quantity) as Total from @Test T where T.BatchID = 'B1' AND Quantity <= T1.Quantity) from @Test T1 where T1.BatchID = 'B1') --select * from Cte_RunningTotal update T set quantity = case when Total <= @Amount then 0 when Total > @Amount and Total - T.Quantity <= T.Quantity then (Total -@Amount) when Total > @Amount and Total - T.Quantity > T.Quantity then T.Quantity end from @Test T inner join Cte_RunningTotal on T.ID = Cte_RunningTotal.ID select * from @TestDo you think it is correct? What if I add records in different order?