Ora-01779: Cannot Modify a Column Which Maps to a Non Key-Preserved Table

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

A DML table expression clause is only useful when you need columns from more than one table. In your case, you can use a regular update with an EXISTS:

update web_userrole
set role = replace(role, 'FULL', 'READ')
where read_only <> 'Y'
    and exists
    (
        select 1/0
        from web_userdatasource
        where datasource = p_datasource
            and username = web_userrole.username
    );

If you really do need to use columns from both tables you have three options:

1. repeat the join in the SET and the WHERE clause. This is easy to build but not optimal.
2. DML table expression. This should work, if you have the correct indexes.

3. MERGE, below is an example.

   merge into web_userrole
   using
   (
       select distinct username
       from web_userdatasource
       where datasource = p_datasource
   ) web_userdatasource on
   (
       web_userrole.username = web_userdatasource.username
       and web_userrole.read_only <> 'Y'
   )
   when matched then update
   set role = replace(role, 'FULL', 'READ');
   

This does not directly answer your question, but instead provides some work-arounds. I can't reproduce the error you're getting. I'd need a full test case to look into it further.

Generic advice for updatable views

One of the main problems with updatable views is the large number of restrictions on the queries they can contain. The query or view must not contain a lot of features, such as DISTINCT, GROUP BY, certain expressions, etc. Queries with those features may raise the exception "ORA-01732: data manipulation operation not legal on this view".

The updatable view query must unambiguously return each row of the modified table only one time. The query must be “key preserved”, which means Oracle must be able to use a primary key or unique constraint to ensure that each row is only modified once.

To demonstrate why key preserved is important, the below code creates an ambiguous update statement. It creates two tables, the first table has one row and the second table has two rows. The tables join by the column A, and try to update the column B in the first table. In this case it's good that Oracle prevents the update, otherwise the value would be non-deterministic. Sometimes the value would be set to "1", sometimes it would be set to "2".

--Create table to update, with one row.
create table test1 as
select 1 a, 1 b from dual;

--Create table to join two, with two rows that match the other table's one row.
create table test2 as
select 1 a, 1 b from dual union all
select 1 a, 2 b from dual;

--Simple view that joins the two tables.
create or replace view test_view as
select test1.a, test1.b b_1, test2.b b_2
from test1
join test2 on test1.a = test2.a;

--Note how there's one value of B_1, but two values for B_2.
select *
from test_view;

A  B_1  B_2
-  ---  ---
1    1    1
1    1    2

--If we try to update the view it fails with this error:
--ORA-01779: cannot modify a column which maps to a non key-preserved table
update test_view
set b_1 = b_2;

--Using a subquery also fails with the same error.
update
(
    select test1.a, test1.b b_1, test2.b b_2
    from test1
    join test2 on test1.a = test2.a
)
set b_1 = b_2;

The MERGE statement does not have the same restrictions. The MERGE statement appears to try to detect ambiguity at run time, instead of compile time.

Unfortunately MERGE doesn't always do a good job of detecting ambiguity. On Oracle 12.2, the below statement will occasionally work, and then fail. Making small changes to the query may make it work or fail, but I can't find a specific pattern.

--The equivalent MERGE may work and changes "2" rows, even though there's only one.
--But if you re-run, or uncomment out the "order by 2 desc" it might raise:
--  ORA-30926: unable to get a stable set of rows in the source tables
merge into test1
using
(
    select test1.a, test1.b b_1, test2.b b_2
    from test1
    join test2 on test1.a = test2.a
    --order by 2 desc
) new_rows
    on (test1.a = new_rows.a)
when matched then update set test1.b = new_rows.b_2;

UPDATE fails at compile time if it is theoretically possible to have duplicates. Some statements that should work won't run.

MERGE fails if the database detects unstable rows at run time. Some statements that shouldn't work will still run.

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

I was able to run my query it by using EXIST clause

UPDATE FCT_RA F
SET F.V_CUST_NUMBER = ( SELECT CASE_TITL_NM 
             FROM KDD_CASES C
             WHERE F.N_RA_ID = C.RA_ID
             AND UPPER(CNTRY_KEY_ID) LIKE '%MANUAL%' 
             AND SCORE_CT IN (99,100)
             AND STATUS_CD = 'CCD'
             AND CASE_TITL_NM NOT LIKE 'MANUAL%')
WHERE EXISTS ( SELECT 1 
             FROM KDD_CASES C
             WHERE F.N_RA_ID = C.RA_ID
             AND UPPER(CNTRY_KEY_ID) LIKE '%MANUAL%' 
             AND SCORE_CT IN (99,100)
             AND STATUS_CD = 'CCD'
             AND CASE_TITL_NM NOT LIKE 'MANUAL%');

Trying to make a simple update but SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table

You can use update but with a correlated subquery:

update table1 t1
    set status = 'NEW'
    where t1.status = 'COMPLETE' and
          exists (select 1
                  from table2 t2 
                  where t1.ID = t2.ID and t2.party is null
                 );

I suspect that you might also want a condition if there are no rows at all in table2 but that logic would not conform to your current query.

How to get rid of ORA 01779 error :key preserved table

If there is no more than one row in table2 corresponding to table1 then use merge:

merge into table1 d
using table2 s
on (d.cg = s.cg and d.ce = s.ce)
when matched then update set
    d.nbqe = s.nbqe, d.adr1 = s.adr1, d.adr3 = s.adr3

Only matching rows will be updated, rest remains untouched. You could do it with update, but this way You avoid repeating condition for where clause.

Test:

create table table1 (nbqe int, adr1 int, adr2 int, adr3 int, cg int, ce int);
create table table2 (nbqe int, adr1 int, adr2 int, adr3 int, cg int, ce int);

insert into table1 values (1, 1, 1, 1, 1, 1);
insert into table1 values (2, 2, 2, 2, 2, 2);
insert into table1 values (3, 3, 3, 3, 2, 2);

insert into table2 values (5, 5, 5, 5, 2, 2);

Result:

NBQE ADR1 ADR2 ADR3   CG   CE
---- ---- ---- ---- ---- ----
   1    1    1    1    1    1
   5    5    2    5    2    2
   5    5    3    5    2    2

Update tables when cannot modify a column which maps to a non key-preserved table

Yes this is possible using two update statements:

update s
    set op_id = (select j.journey_id
                 from j
                 where j.op_id = s.op_id
                );

and:

update op
    set op_id = (select j.journey_id
                 from j
                 where j.op_id = op.op_id
                );

ORA-01779: cannot modify a column which maps to a non key-preserved table on updating joined tables

Updating a query is always tricky. Try a merge instead.

MERGE INTO uas_mapping_test t1
USING 
(   
  select pt.poid_id0, umt.plan_id
    from norie.plan_t_test pt 
    join norie.UAS_MAPPING_TEST umt on (pt.poid_id0 = umt.plan_id)
) ta ON poid_id0 <> plan_id
WHEN MATCHED THEN UPDATE 
set plan_id = poid_id0

Cannot modify a column which maps to a non key preserved table in pl/sql

Unfortunately, you will likely end up rewriting this with more modern MERGE syntax or doing the update in a different manner.

Your code is using an inline view as an updatable view, and there are some restrictions as to when you can update a view.

Oracle docs on views, (search for "Notes on updatable views")

A Look at Oracle Updatable Views

---

Related Topics