Re: A question about performing UPDATE and multiple tables

On Fri, 07 Sep 2007 13:15:43 -0400, Ubiquitous <weberm_at_polaris.net> wrote:

>ken_at_kendenny.com wrote:
>>On Sep 7, 11:31 am, Ubiquitous <web..._at_polaris.net> wrote:
>
>>> I currently have a stored procedure which reads a transaction table for new
>>> records, writes them to a flat file (which is uploaded to a mainframe system
>>> for processing) and then updates the records as being processed.
>>>
>>> We will be creating a new ORACLE instance which also creates transactions. For
>>> network traffic and other issues, we will create an indentical transaction
>>> table in the new instance that will be processed the same was as the original.
>>>
>>> My current stored procedure looks something like this (simplified a bit for
>>> clarity's sake):
>>>
>>> /************************************************/
>>> CURSOR GetTrans_cur IS
>>> SELECT *
>>> FROM TRANS
>>> WHERE processing_dt IS NULL
>>> FOR UPDATE OF processing_dt;
>>>
>>> [...]
>>>
>>> FOR v_gettrans IN GetTrans_cur LOOP
>>> [build file record from cursor fields]
>>> UTL_FILE.PUT_LINE(v_outfile, v_REC);
>>>
>>> UPDATE TRANS
>>> SET processing_dt = SYSDATE
>>> WHERE CURENT OF GetTrans_cur;
>>> END LOOP;
>>> /************************************************/
>>>
>>> The easiest way to process the new table would be to create a second cursor
>>> and perform a second FOR LOOP for it, but is there a way to query and update
>>> multiple tables with a single cursor?
>>>
>>> I thought using a UNION would be perfect for the task but it doesn't like
>>> having an UPDATE OF clause. Am I barking up the wrong tree?
>>
>>CURSOR c1 IS SELECT t1.col1, t1.col2, t2.col3, t2.col4
>> FROM t1, t2
>> WHERE t1.id = t2.id
>> FOR UPDATE OF t1.something, t2.something;
>>
>
>Thank you! I never considered using a normal join.
>
>Can someone point me to where I can find the differences between a join and a UNION of two tables?
>I'm curious...

UNION and a join are two different animals. UNION appends the records from the second query onto the result set of the first query. A join makes it as if it were wider TABLE.

B. Received on Mon Sep 10 2007 - 08:21:48 CDT