How can I prevent ORA-01779 Error:cannot modify a column which maps to a non-key-preserved table

From: Thomas <echo88de_at_yahoo.de>
Date: 24 Nov 2006 06:12:35 -0800
Message-ID: <1164377555.180193.153270@l12g2000cwl.googlegroups.com>

Hello together,

I come back to you because the last time I had a question You gave me very good solutions. The last time you told me somthing about updateable view and now I have a problem using them in the way I want to...
The situation is I have three tables A,B,C all with one identical column named customer number (custnbr). I want to update column 'is_unique' in table A if the customer is not in table B and not in table C.
I know constructions with "where custnbr not in (select ...)" will do that but are VERY slow since the tables are quite huge. So I revert back to my threat from 12 Dec 2005 where Jonathan Lewis answered to use an updateable view would be best.(Never heard of that before)
I did that:

update
(
  select is_unique from A ,
    (select custnbr from A

     minus
     select custnbr from B
     minus
     select custnbr from C

    ) temp
where A.custnbr=temp.custnbr
)
set is_unique='Y';

But Oracle punished me with the Error
ORA-01779:cannot modify a column which maps to a non-key-preserved table

How can I realize to update the coumns in A for Customers not in B and C??

Thanks in advance...

...and greetinx from Germany
Thomas Received on Fri Nov 24 2006 - 08:12:35 CST