Skip to Main Content
  • Questions
  • Trying to update from a join ang getting: ORA-01779: cannot modify a column which maps to a non key-preserved table

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Phil.

Asked: November 21, 2011 - 9:41 pm UTC

Last updated: November 22, 2011 - 7:15 pm UTC

Version: 10.2.0

Viewed 10K+ times! This question is

You Asked

Okay, I see some other people have asked questions about this error:
ORA-01779: cannot modify a column which maps to a non key-preserved table

I still can't figure it out. I would appreciate any help you can provide. Here is my scenario: I am trying to update multiple fields on specific records in a table based on other rows in the same table.
I am sure that there is a one-to one match in my join, but I still get that error.
CREATE TABLE MY_RATIOS
(
  SEQ_NO                          NUMBER(9)     NOT NULL,
  SCENARIO                        VARCHAR2(250 BYTE) NOT NULL,
  REC_TYP                         VARCHAR2(2 BYTE) NOT NULL,
  RATIO_1                         FLOAT(126),
  RATIO_2                         FLOAT(126));
alter table MY_RATIOS add constraint t2_pk primary key(SEQ_NO, SCENARIO, REC_TYP);

Insert into MY_RATIOS (SEQ_NO, SCENARIO, REC_TYP, RATIO_1, RATIO_2) Values (1, 'current year', 'AR', 25, 75);
Insert into MY_RATIOS (SEQ_NO, SCENARIO, REC_TYP, RATIO_1, RATIO_2) Values (1, 'forecast', 'AR', 0, 0);
Insert into MY_RATIOS (SEQ_NO, SCENARIO, REC_TYP, RATIO_1, RATIO_2) Values (2, 'current year', 'AR', 10, 90);
Insert into MY_RATIOS (SEQ_NO, SCENARIO, REC_TYP, RATIO_1, RATIO_2) Values (2, 'forecast', 'AR', 0, 0);
Insert into MY_RATIOS (SEQ_NO, SCENARIO, REC_TYP, RATIO_1, RATIO_2) Values (3, 'current year', 'AR', 92, 8);
Insert into MY_RATIOS (SEQ_NO, SCENARIO, REC_TYP, RATIO_1, RATIO_2) Values (3, 'forecast', 'AR', 46, 54);
Insert into MY_RATIOS (SEQ_NO, SCENARIO, REC_TYP, RATIO_1, RATIO_2) Values (4, 'current year', 'AR', 18, 82);
Insert into MY_RATIOS (SEQ_NO, SCENARIO, REC_TYP, RATIO_1, RATIO_2) Values (5, 'forecast', 'AR', 51, 49);
Insert into MY_RATIOS (SEQ_NO, SCENARIO, REC_TYP, RATIO_1, RATIO_2) Values (3, 'current year', 'AX', 28, 72);

So, where there are records where scenario='forecast' and rec_typ='AR' and the sum of the ratios=0, I want to update the ratios with the ratios from the corresponding records from the same table that have the same seq_no, rec_typ and scenario='current year' without impacting any of the other records.

Here is my attempt at the sql:
update ( 
select a.seq_no, a.RATIO_1 as new_RATIO_1, a.RATIO_2 as new_RATIO_2, b.RATIO_1, b.RATIO_2 from my_ratios a, my_ratios b
where a.SEQ_NO = b.SEQ_NO 
and a.scenario = 'forecast' and b.scenario = 'current year'
and a.rec_typ = b.rec_typ
and a.rec_typ = 'AR'
and a.RATIO_1 + a.RATIO_2 = 0
)
set new_RATIO_1 = RATIO_1, new_RATIO_2 = RATIO_2;


this results in:
ORA-01779: cannot modify a column which maps to a non key-preserved table

Here are a couple of things I tried which might help:
1) If I just run the select statement from the sql above, I get the expected results.
select a.seq_no, a.RATIO_1 as new_RATIO_1, a.RATIO_2 as new_RATIO_2, b.RATIO_1, b.RATIO_2 from my_ratios a, my_ratios b
where a.SEQ_NO = b.SEQ_NO 
and a.scenario = 'forecast' and b.scenario = 'current year'
and a.rec_typ = b.rec_typ
and a.rec_typ = 'AR'
and a.RATIO_1 + a.RATIO_2 = 0


Results:
seq_no new_ratio_1 new_ratio_2 ratio_1 ratio_2
1 0 0 25 75
2 0 0 10 90

2) If I change this line in the where clause:
and a.scenario = 'forecast' and b.scenario = 'current year'
to
and a.scenario = b.scenario

I don't get the error, but doesn't do what I need it to do.

Please keep in mind that I am not a DBA and do not have access to modify any tables or indexes, so a solution that does not require that is preferred.

Thanks Tom!

and Tom said...

Here is the problem:


and a.scenario = 'forecast' and b.scenario = 'current year'


the optimizer doesn't recognize that is sufficient to get just one record (given the other two join conditions).

But, there is a simple way to solve this:

ops$tkyte%ORA11GR2> merge into
  2  (select *
  3     from my_ratios
  4    where scenario = 'forecast'
  5      and rec_typ = 'AR'
  6      and ratio_1+ratio_2 = 0
  7  ) a
  8  using
  9  (select *
 10     from my_ratios
 11    where scenario = 'current year'
 12      and rec_typ = 'AR'
 13  ) b
 14  on (a.seq_no = b.seq_no)
 15  when matched then update set a.ratio_1 = b.ratio_1, a.ratio_2 = b.ratio_2;

2 rows merged.



Identify the rows you want to update as A, identify the rows you want to update them with as B, and away you go....


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Are you sure this works in 10.2?

Phil J, November 22, 2011 - 3:57 pm UTC

Wow, my question was answered by Ask Tom. I'm kvelling!

I don't think this works in 10.2.0. When I run the solution in a 10.2.0 instance, I get this error:

ORA-00903: invalid table name

As test, I ran the same example in a 11.2.0 instance and it worked. I see also that you were in what looks like 11.2 (based on your command prompt).

It seems like that statement doesn't work in 10.2. Would you agree? Do you have a suggestion for this that would work in 10.2?

Thanks again for your help - On this and throughout the years!
Tom Kyte
November 22, 2011 - 7:15 pm UTC

ops$tkyte%ORA10GR2> merge into
  2  (select *
  3     from my_ratios
  4    where scenario = 'forecast'
  5      and rec_typ = 'AR'
  6      and ratio_1+ratio_2 = 0
  7  ) a
  8  using
  9  (select *
 10     from my_ratios
 11    where scenario = 'current year'
 12      and rec_typ = 'AR'
 13  ) b
 14  on (a.seq_no = b.seq_no)
 15  when matched then update set a.ratio_1 = b.ratio_1, a.ratio_2 = b.ratio_2;

2 rows merged.

ops$tkyte%ORA10GR2> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production



but if there was an issue in an earlier release (earlier than 10.2.0.5), you can use the view approached mentioned below.

RE: "optimizer doesn't recognize that is sufficient ..."

Duke Ganote, November 22, 2011 - 5:01 pm UTC

The Database Administrator's Guide says "The key-preserving property of a table does not depend on the actual data in the table. It is, rather, a property of its schema." In other words, the DATA DOESN'T MATTER, only the data structure does.

Apparently, if the optimizer doesn't recognize a simple equijoin using the entire primary key of the DEPT, it quickly throws:

ORA-01779: cannot modify a column which maps to a non key-preserved table

That's during the query optimization steps. It'll throw the error even if the tables are empty!

By contrast, a MERGE or standard UPDATE will gamely *try* to execute the update. After it has some or all of the actual results, it tests the results for uniqueness/key-preservation. If the results aren't satisfactory, the execution engine throws:

ORA-30926: unable to get a stable set of rows in the source tables

http://docs.oracle.com/cd/B10500_01/server.920/a96521/views.htm#4054

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:548422757486#1364745400346044562

A reader, November 22, 2011 - 7:03 pm UTC

Hello Phil,you can create a view instead,and then using merge.

create view test_v as select *
from my_ratios
where scenario = 'forecast'
and rec_typ = 'AR'
and ratio_1+ratio_2 = 0;

merge into
test_v a
using
(select *
from my_ratios
where scenario = 'current year'
and rec_typ = 'AR'
) b
on (a.seq_no = b.seq_no)
when matched then update set a.ratio_1 = b.ratio_1, a.ratio_2 = b.ratio_2;

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.