>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 @Test >>