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

Thomas 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

You are trying to update a view ... not a table thus the error.

Write a SQL statement that will find records not in B and C, try using NOT EXIST, and then update A when the condition is TRUE.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org