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

Try:
UPDATE PHONECALLS
   SET (posted_date, amount) =

         ( SELECT a.posted_date, a.amount
             FROM NEWDATA a
            WHERE a.duration_seconds = PHONECALLS.duration_seconds
              AND a.duration_minutes = PHONECALLS.duration_minutes
              AND a.destination_number = PHONECALLS.deatination_number

              AND a.origin_number = PHONECALLS.origin_number
              AND a.call_date = PHONECALLS.call_date
              AND a.person_id = PHONECALLS.person_id ) ;

Jerry.

Boyce G. Williams, Jr. wrote:

> Hi,
>
> Thanks to all that answered a previous question I posted. Now I've
> got another puzzle...
>
> Being a DB2, MS-Access, and SAS SQL man and given an assignment to
> update a table from another in Oracle7, I naturally wrote an update
> query like this:
>
> UPDATE NEWDATA INNER JOIN PHONECALLS
> ON NEWDATA.duration_seconds = PHONECALLS.duration_seconds
> AND NEWDATA.duration_minutes = PHONECALLS.duration_minutes
> AND NEWDATA.destination_number = PHONECALLS.deatination_number
> AND NEWDATA.origin_number = PHONECALLS.origin_number
> AND NEWDATA.call_date = PHONECALLS.call_date
> AND NEWDATA.person_id = PHONECALLS.person_id
> SET
> PHONECALLS.posted_date = NEWDATA.posted_date
> PHONECALLS.amount = NEWDATA.amount ;
>
> However, I discovered to my dismay that Oracle doesn't have an INNER
> JOIN clause in its syntax set. So I researched for an
> Oracle7-accecptable way of doing the same trick. Adapting Joe Celko's
> "SQL for Smarties", page 124, to my problem I get:
>
> UPDATE PHONECALLS
> SET posted_date =
> ( SELECT a.posted_date
> FROM NEWDATA a
> WHERE a.duration_seconds = PHONECALLS.duration_seconds
> AND a.duration_minutes = PHONECALLS.duration_minutes
> AND a.destination_number = PHONECALLS.deatination_number
>
> AND a.origin_number = PHONECALLS.origin_number
> AND a.call_date = PHONECALLS.call_date
> AND a.person_id = PHONECALLS.person_id ) ,
> amount =
> ( SELECT b.amount
> FROM NEWDATA b
> WHERE b.duration_seconds = PHONECALLS.duration_seconds
> AND b.duration_minutes = PHONECALLS.duration_minutes
> AND b.destination_number = PHONECALLS.deatination_number
>
> AND b.origin_number = PHONECALLS.origin_number
> AND b.call_date = PHONECALLS.call_date
> AND b.person_id = PHONECALLS.person_id ) ;
>
> This leaves me with a big problem. I ended up with two correlated
> subqueries which I know will grind our machine to a halt. Can someone
> please provide a suggestion how I can update two or more columns from
> one table to another without the need to do it through correlated
> subqueries as shown above?
>
> Again thanking all in advance,
> Boyce G. Williams, Jr.
> 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|
> '--------------------------------------------------------------------'
Received on Thu Mar 18 1999 - 10:05:36 CST