how to use updaterecordset and order by recid clause

Hi guys,

I have a serial owner table which has 1 lakh records and i have serial trans table it has more number of records says 3 lakhs , I have to update counterpartner field in serial owner from serial trans and I have to select the last recid in the child table.

in short, I have to use updaterecordset and order by recid .


while select forUpdate DMSSerialNumOwner
select firstOnly * from DMSSerialNumTrans order by recid desc

where DMSSerialNumTrans.SerialNum == DMSSerialNumOwner.SerialNum;

DMSSerialNumOwner.counterpartner = DMSSerialNumTrans.counterpartner ;




I will be happy if some one convert the above code in updaterecordset.



First of all, isn’t this duplicated data a sign of incorrect database design? Maybe if you explain your business requirement, we’ll be able to suggest a better solution.

How do you know that last line (and not the other lines) has the right value?

If you believe that your design is correct, you can look at my solution for joining first lines and modify it to get last lines.

For reference, the same question is also being discussed on another forum.