Tuesday, January 22, 2008

Update with join and ORA-01779

A very fast update when we want to update 2 tables T1 ,T2 having a join on a column and unique values on the join.(One row form T1,matches only with one row from T2)

update (
select a.ordering a1,b.ind a2
from T1 a,T2 b
where a.cmp_id=139
and a.cmp_key=b.k)
set a1=a2;

For the given cmp_id any cmp_key is unique on table T1.
Also for any k on T2 there must be only one value for ind.
So we need a primary key on T2.k in order to work the query and avoid the ORA-01779 error.

1 comment: