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 don't know why the nvl() isn't preventing the update with NULL. However, I do have an
alternative suggestion, which has these qualities which I think you want:

1. Uses an index on a.key, but doesn't assume an index on b.key.
2. Does one full table scan on b, and only index-match access on a.

You could use a PL/SQL loop to force the whole process to be driven by table b:

DECLARE CURSOR B_CUR IS SELECT KEY,VAL FROM B; BEGIN FOR B_REC IN B_CUR LOOP UPDATE A SET VAL = A.VAL + B_REC.VAL WHERE A.KEY = B.KEY; END LOOP;
END;
/

This should work efficiently, especially if B is much smaller than A and A is indexed as I expect. Received on Wed Apr 09 1997 - 00:00:00 CDT