Re: Problems with correlated update

Hank Eskin (henry_at_ix.netcom.com) wrote:
: I think I am trying to do something really simple -
: and I apologize if this has been asked before.
 

: I have 2 identical tables (A&b), and I want to update
: table A with new data from table B, only where the keys match.
: I have an update statement like this:
 

: update a set (a.val)=
: (select a.val+nvl(b.val,0)
: from b
: where a.key=b.key(+));
 

: So, you see, I want to add Val from B to Val of A, and update back
: to A the new values, but only for the records in B. The above statement
: will update all rows of A, putting nulls in where there was no matching
: record in B. I realize I could add to the end of the update statement a
: :
 

: WHERE a.key in (select distinct key from b)
 

: but that is a very inefficient way to do it. (the tables really have
: four keys the in clause would look like this:
 

: Where a.key1||a.key2||a.key3||a.key4 in
: (select distinct b.key1||b.key2||b.key3||b.key4 from b)
 

: Any help would be greatly appreciated.
 

: Thanks in advance!
 

: - Hank
 

: --
: netcom: henry_at_ix.netcom.com

--
Hi,

Whether to use subqueries with 'in' or correlated subqueries (in the 
where clause) depends on the size of the tables. 'IN' does a sort unique 
(and thats why the 'distinct' is not necessary) on a full table scan and then
a sort merge join with the table beeing updated. This might be resource 
consuming.

I would suggest you try someting like this:

update a set a.val = (select a.val + b.val from b where b.key = a.key)
where exists (select null from b where b.key = a.key)
/

If the tables are large i would recommend against a PL/SQL loop suggestion.
(ie. for every record in b loop, update corresponding in a). If b contains 
one millon records this would mean that you would perform one million update 
statetemts sequentially. Althoug each statement is small it takes some time.
If it only takes one 100th's of a second you still would need 0.01 * 1 Mill
seconds, which is 3 hours.  

Rgds
Steinar Heggelund

----------------------------------------------------------------------------
I'm employed in the Norwegian consulting company Opus One AS. 
I have 7 years experience with Oracle products, mainly the database. 
We are a small company which offers consulting services in design,
implementation and tuning of databases and applications based on Oracle.

My postings represent my opinions and they may be wrong.