It is also possible, but why you didn't like the solution I provided (from the top of my head but should work). The trick here is that you can use

UPDATE cte statement.

>Thank you for your suggestion.
>
>But, are you saying that what I am trying to do is not possible with UPDATE and (SELECT TOP)?
>
>>>However, what do you want when @QtyUse is greater than qty_left? I assume you want to keep subtracting it from other rows - this may be slightly more tricky problem.
>
>Yes, I will set up a While and then find another record to get some more QTY_LEFT. But, first, I want to find an approach for one record. And then get into the "tricky problem"
>
>>Hi,
>>
>>Try this
>>

>>declare @priceTable table (unitPrice money, finalQty int);
>>
>>with cte as (select *, row_number() over (order by date_rec) as Rn from myTable)
>>
>>update cte set qty_left = qty_left - @QtyUse 
>>output inserted.unitPrice, inserted qty_left into @PriceTable (unitPrice, FinalQty)
>>where Rn = 1;
>>
>>select * from @PriceTable; -- final qty and unit price 
>>

>>
>>However, what do you want when @QtyUse is greater than qty_left? I assume you want to keep subtracting it from other rows - this may be slightly more tricky problem.
>>
>>
>>>Hi,
>>>
>>>This is a simplified case.
>>>
>>>The table (structure and sample data is as follows)
>>>

>>>PK      PART_PK   DATE_REC    QTY_LEFT    REC_ID      UNIT_PRICE
>>>1       1         01/01/2019     10         '1'         4.00
>>>2       1         02/01/2019     4          '1'         5.00
>>>3       1         12/14/2018     7          '1'         5.00
>>>4       1         04/05/2017     0          '2'         3.50
>>>
>>>

>>>
>>>I am trying to create a SQL that would do the following:
>>>1. A parameter is passed as follows:
>>>@QtyUse as Int
>>>The value of @QtyUse could be from 0 to any number. For example, 1
>>>2. Find the oldest record in the above table (date 12/14/2018)
>>>Reduce the value in QTY_LEFT by the @QtyUse and update the @QtyUse
>>>3. Set the value of a variable @UnitPrice to the value of the column UNIT_PRICE (of the effected record)
>>>
>>>Here is my attempt (not successful)
>>>

>>>declare @QtyUse as Int
>>>set @QtyUse = 1
>>>update t1 set t1.qty_left = t1.qty_left -  @QtyUse (select top 1 t2.pk from MyTable t2 where t2.rec_id = '1' 
>>>   order by t2.date_rec) , from MyTable t1 where t1.pk = t2.pk
>>>

>>>
>>>Please let me know how to change the above SQL?
>>>
>>>TIA