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

In article <36f1118f.4993611_at_usenet.acw.vcu.edu>,

        bgwillia_at_vcu.edu (Boyce G. Williams, Jr.) writes:
>
> 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?

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;

The "UPDATE ... WHERE CURRENT OF" is very fast, since Oracle already knows the rowid of the row it has to update.

Remco
--
rd1936: 2:10am up 10 days, 8:34, 8 users, load average: 1.04, 1.07, 1.08 Received on Tue Mar 23 1999 - 19:27:21 CST