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;
>
>
>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
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham