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!