Subtracting sequentially from multiple sources

[nsyr]

i have a simplified table with "BoxID, Item, Quanity" and I want to subtract the Quanity of the ProductID from the BoxID sequentially. i.e. the lowest BoxID first. My current code is as follows:

update boxes
set quanity = (quanity - itemshipped.qty shipped )
from itemshipped
where (item = boxes.itemno) and seqid in (select min(seqid) from boxes where (quanity > 0)) and (pickseq = boxes.location) and (boxes.quanity > 0) and not exists(select shippedid from itemshippedcomp lete where (shippedid = itemshipped.shi ppedid))

The problem that I am having is that if I have a quanity sold greater than what is left in the first box then I am left with a negative quanity. What I need it to do is subtract until quanity is 0 then subtract the remaining quanity from the next box quanity.

So if sold a quanity of 4 of 'item A' then my table should go from:

box 1, item A, qty 2
box 2, item A, qty 10

to:

box 1, item A, qty 0
box 2, item A, qty 8

instead of:

box1, item A, qty -2
box2, item A, qty 10

[ck9663]

I was trying to think of a single (or group of) T-SQL that can handle your requirement. However, the challenge is if this is in a multi-user environment, that would mean your item could be coming from non-sequential boxes. In this case, your only choice could be to handle it using a loop from your front-end and keep finding boxes with available item.

-- CK

[nsyr]

The quanity sold is coming from a trigger in another table. This part of it is essentially single user. The boxes get reduced when the items are shipped.

[ck9663]

Loop might be your only choice.

-- CK