Re: Problems with correlated update

Hank Eskin 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

The IN clause is desirable and avoids the necessity for an outer join, but not in the form you wrote it.
Try:

where (a.key1, a.key2, a.key3, a.key4) in (select b.key1, b.key2, b.key3, b.key4 from b) The DISTINCT is redundant in subqueries.

Hope this helps.

Chrysalis. Received on Tue Apr 08 1997 - 00:00:00 CDT