It is possible to do this using a loop - BUT just because you
can do something does not mean that you should.
First, a working example. This code snippet will do what you want assuming that you assign id's to each row based on the order they are added to the database. You could just as easily use a datetime as long as you have something that implies order.
declare @prod table (id int identity(1,1), Product char(1), amount int);
insert into @prod values ('A', 100), ('A', 100);
declare @buy int = 120;
declare @work int;
declare @id int;
WHILE @buy > 0
BEGIN
SELECT TOP 1 @id = id, @work = amount FROM @prod WHERE Product = 'A' AND amount > 0 ORDER BY id DESC
IF @work <= @buy
UPDATE @prod SET amount = 0 WHERE id = @id
ELSE
UPDATE @prod SET amount = amount - @buy WHERE id = @id
SET @buy = @buy - @work
END
select * from @prod
SQL is a set-based language so you really shouldn't be doing this loop SQL-side but in your UI perhaps.
The principle here is
- Make sure you access the rows in the table in the "correct" order - I have used ORDER BY id in the example, but you could use a datetime column to record when the stock arrived. The DESC ensures LIFO of which more below
- Remove as much as you need from the first row that has a positive amount
- keep removing stock from those rows (in that order) until you have used up all that you wanted to extract - see variable @buy
- "remove" means adjust the stock level (amount in my example) by the maximum possible - hence if the first row encountered does not fulfill the amount then set it to 0
Now to a couple of other points.
- Inventory Management usually handles stock in FIFO order (First In, First Out) so that stock does not get out of date, so I question your logic.
- Having two rows per product on a table only makes sense if you are also capturing a batch number or similar, and you are using this query to "pick" boxes from the inventory in the correct order. If you are just concerned with the amount of each product in your "warehouse" then you would only have ONE row per product and the amount is adjusted accordingly with each sale or delivery.