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

From: Galen Boyer <galen_boyer_at_yahoo.com>
Date: 25 Nov 2006 07:54:03 -0600
Message-ID: <u8xhzpicm.fsf@rcn.com>

On 24 Nov 2006, 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

This error is Oracle saying it will not update a column if there is more than one answer found per key in the join sql.

Without any type of playing around I would fiddle with a few of these options:

1. It may be as simple as giving the outer parentheses an alias as well and then updating by alias. (Its been awhile, but I always found this syntax to be very powerful but sometimes took a little bit of fiddling to get Oracle to agree that it was valid.)

    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
    ) temp2
    set temp2.is_unique='Y';

2) Is custnbr a unique column in A, B and C?

3) It looks like Oracle may be thinking that you are trying to update A

    with custnbrs found in B or C (Yeah, I know, you explicitly join to     temp by A.custnbr. Maybe you could try being more explicit with     "temp" the SQL: (I think Oracle uses the written SQL logic to give     you this error, not data results)

         (select custnbr from A
          minus
             (select custnbr from B
              minus
              select custnbr from C)

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

Good luck. I haven't coded in Oracle in a few months. Can't wait to get going again. These kind of problems are rewarding when answered.

-- 
Galen Boyer

Received on Sat Nov 25 2006 - 07:54:03 CST