This is a discussion on How to update 2 records simultaneously on a self-join? within the Oracle Miscellaneous forums, part of the Oracle Database category; --> When using the Oracle syntax below I need to update pairs of records that meet some criterion: Update ( ...
| |||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
| ||||
| 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... |