>>>>In the UPDATE I set the new value of the column QTY_LEFT. And then, based on the value in this column, I have to set the value of the column REC_ID. So, my understanding - since the order of evaluation is not certain - to use the New Value of the column QTY_LEFT when setting the value of REC_ID.
>>>>So, my question, can I SET the value of column REC_ID
after I have the OUTPUT command?
>>>
>>>It will be a new separate UPDATE command.
>>
>>Then, if I understand this correctly, I have to do the Select Top(1) twice. That is, the "long" UPDATE command finds the oldest (chronologically) record in the table. Then it update the column QTY_LEFT.
>>Then, I have to find this record again, in order to use the OUTPUT variable and update the column REC_ID.
>>Do I understand the sequence correctly?
>
>If your table has PK column, get it in OUTPUT table.
>
>May be you if you give more information of what you're trying to achieve, post DDL and some info of before and after, you'll get better response.
I appreciate your time and your suggestions very much. Before I post the DDL, I decided to change the approach. I will STOP relaying on the column REC_ID but ONLY on the column QTY_LEFT. So that in the inner SELECT instead of t2.rec_id = '1' I changed to t2.qty_left > 0. I tested it and it works. And then, after I do all the updates of the QTY_LEFT I will run one UPDATE and set the value of REC_ID to '2' for those rows where the quantity is 0 or less.
"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