How to update 2 records simultaneously on a self-join?

When using the Oracle syntax below I need to update pairs of records that meet some criterion:

  Update

            (
  Select/*+bypass_ujvc */ t1.fieldA A, t2.fieldB B, t2.mark mark    from mytab t1, mytab t2
   where t1.fieldC > t2.fieldC
   and t1.columnD = t2.columnD
  )
  set A =B, mark=1; --<===

  The phrase ,mark = 1 is not allowed because it updates the other record of the pair.

  IS there any way to update an Oracle table using pairs of records, update both records simultaneously and preferably proceed in descending order on fieldC?

  This situation occurs repeatedly in a topological problem that focuses on relationships between 2 things. Using another language one would sort the records on columnD and then loop on pairs of records with the same columnD value. If just records 1 and 2 have this property then the problem is simple, but if there are more than 2 (say 3) then we need to examine the relationship between 1 and 2, 1 and 3 and 2 and 3. If the records are sorted on columnD,fieldC we can simplify and just process 1 and 2, then ignore record 3 if there are only 3 or process 3 and 4 when there are 4 with the same columnD value.

  I have seen a use of a trigger to perform some "automatic" update of the other record of a pair, but I don't know how to code that.

   Thanks for any insights... Received on Sun Aug 27 2006 - 09:49:35 CDT