Thank you! This works without an error.

Now, I have to ask you. Would your suggestion of using cte be faster or more - how do I say it, "efficient" - code?

>This is how you can use your original idea:
>
>

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

>
>
>
>>A joke:
>>
>>A Jewish mother gives her son two ties on the first night of Hanukkah. The following morning, when he comes down for breakfast, he is wearing one of them. The mom says, “What’s the matter — you didn’t like the other one?”
>>
>>Do you get why I am sharing it with you? :)
>>
>>>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