Take a look at the similar question here

http://stackoverflow.com/questions/22936112/fifo-implementation-in-inventory-using-sql

>For now ignore in this sample code the column 'rec_id'
>

>declare @Used decimal (12,2)
>set @Used = 7
>create table #invstock (inv_code char(20), qty_left decimal(10,2), inven_pk int, unit_price decimal (12,2), rec_id char(1))
>go
>insert into #invstock (inv_code, qty_left, inven_pk, unit_price, rec_id) values 
>	('ABC', 15, 1, 1.93, '1')
>insert into #invstock (inv_code, qty_left, inven_pk, unit_price, rec_id) values 
>	('ABC', 4, 2, 1.93, '1')
>insert into #invstock (inv_code, qty_left, inven_pk, unit_price, rec_id) values 
>	('ABC', 7, 3, 1.93, '1')
>insert into #invstock (inv_code, qty_left, inven_pk, unit_price, rec_id) values 
>	('ABC', 8, 4, 1.93, '1')
>

>
>I need to create a SQL Update that will change the value of each record column QTY_LEFT subtracting the value of @Used.
>That is, if the value of @Used is 7, only the first row will change the value of QTY_LEFT to 8. But if the value of @Used in 20, then the first row will have the value of QTY_LEFT as 0, the second row will be 0, and the 3rd one will have QTY_LEFT as 6.
>
>This is one small part of the overall SQL I am trying to create but I need to break it down so that I can understand it.
>
>>Can you provide more details?
>>