Unix Technical Forum

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

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 ( ...


Go Back   Unix Technical Forum > Database Server Software > Oracle Database > Oracle Miscellaneous

FAQ Members List Calendar Search Today's Posts Mark Forums Read
  #1 (permalink)  
Old 04-08-2008, 11:14 AM
sideyt
 
Posts: n/a
Default 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...

Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On
Forum Jump


All times are GMT. The time now is 06:12 AM.


Powered by vBulletin® Version 3.6.5
Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
Search Engine Optimization by vBSEO 3.2.0
www.UnixAdminTalk.com