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

On 24 Nov 2006 06:12:35 -0800, "Thomas" <echo88de_at_yahoo.de> wrote:

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

Hi,

I stumbled on a similar problem on May 5 2004. I think the following answer given by Jonathan Lewis (indicated by #) also applies to your case:

# Your analysis is correct - the table is key-preserved,
# and Oracle 'ought' to recognise it.
#
# There are other, cases where the optimizer fails to spot
# the key preservation.  One day, no doubt, they will
# be covered.
# 
# -- 
# Regards
# 
# Jonathan Lewis 

Regards, Jaap. Received on Sat Nov 25 2006 - 11:18:53 CST