Re: Updating a table from another - how to avoid a correlated subquery

remco_at_rd1936.quicknet.nl (Remco Blaakmeer) wrote:

>Try it in PL/SQL, like this (this is _untested_ code):
>
>DECLARE
> CURSOR cur_phone IS
> SELECT n.posted_date
> , n.amount
> FROM phonecalls p
> , newdata n
> WHERE n.duration_seconds = p.duration_seconds
> , n.duration_minutes = p.duration_minutes
> , n.destination_number = p.destination_number
> , n.origin_number = p.origin_number
> , n.call_date = p.call_date
> , n.persion_id = p.persion_id
> FOR UPDATE OF phonecalls.posted_date, phonecalls.amount;
>BEGIN
> FOR row_phone IN cur_phone LOOP
> UPDATE phonecalls
> SET posted_date = row_phone.posted_date
> , amount = row_phone.posted_date
> WHERE CURRENT OF cur_phone;
> END LOOP;
>END;
>/
>
>The "UPDATE ... WHERE CURRENT OF" is very fast, since Oracle already
>knows the rowid of the row it has to update.
>
>Remco

I know I'm going off on a tangent on this, but I've never written a PL/SQL program and always assume one has to write it in C or COBOL (which I've done in DB2). I have written SQL scripts and executed them as "@someprogram" from the "SQL>" prompt. Can the same be done with PL/SQL without using compiled languages?

Thanks in advance,

Boyce G. Williams, Jr.

 .--------------------------------------------------------------------.
 | "People should have two virtues:  purpose- the courage to envisage |
 | and pursue valued goals uninhibited by the defeat of infantile     |
 | fantasies, by guilt and the failing fear punishment;  and wisdom- a|
 | detached concern with life itself, in the face of death itself."   |
 |                                                     Norman F. Dixon|
 '--------------------------------------------------------------------'