Update order items with box quantity

Question

I am trying to update a shipping database that is set-up incorrectly from the start with table structure, but am having to work with it (for now).

What I need is for example with the DDL below there were 3 total shipments for the saleId- what I need to have the query do is:

Create Table Testing
(
  saleId int
  ,totalQty int
  ,itemDescription varchar(250)
  ,lineItem int
  ,maxAllowedInBox int
  ,itemsInBox int
  ,totalBoxesShipped int
)

Insert Into Testing Values
('123', 50, 'shirt', 1, 21, 0, 3)
,('123', 50, 'socks', 2, 21, 0, 3)
,('123', 50, 'hat', 3, 21, 0, 3)

Update the value of itemsInBox to 21, 21, 8 because 21+21+8 = 50 (the max allowed) This is just a subset of data, but it illustrates what I am needing to do. How can I write a SQL Server query to handle this?

I tried this update query, but it inaccurately updates because it's not accounting for everything as I need. :(

Update Testing
Set itemsInBox = 
case
      when [maxAllowedInBox] < totalQty then [maxAllowedInBox] 
      else [totalQty]-[maxAllowedInBox] 
end

Answer 1

use sum() with window function to get cumulative total and allocate to maxAllowedInBox to all lines except the last one

update t
set    itemsInBox = case when cumTotal <= totalQty 
                         then maxAllowedInBox
                         else totalQty - cumTotal + maxAllowedInBox
                         end
from
(
    select *, 
           cumTotal = sum(maxAllowedInBox) over (partition by saleId 
                                                     order by lineItem)
    from   Testing
) t

db<>fiddle demo