Update script errors

Question

I am trying to update some missing data in our oracle db via PL SQL developer. I have tried two separate update variation but I keep getting error:

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

Anyone got any ideas please.

    Update  (Select i.involvement_id, i.open_date, i.close_date, i.status_id,  
            iof.outcome_code, iof.involvement_form_outcome_id, 
            ifm.description 

     FROM   involvement i, involvement_form ifm, involvement_form_outcome  
            iof, involvement_outcome io

     WHERE   i.involvement_form_id = ifm.Involvement_form_id (+)
     and     i.involvement_id = io.involvement_id (+) 
     and     io.involvement_form_outcome_id = 
             iof.involvement_form_outcome_id (+)
     and     ifm.description = 'Midnight League' and iof.outcome_code is 
             null)iof
     SET     iof.outcome_code = 'ENI'


     Update  (Select  * FROM involvement i, involvement_form ifm,  
                involvement_form_outcome iof, involvement_outcome io
     WHERE   i.involvement_form_id = ifm.Involvement_form_id (+)
     and     i.involvement_id = io.involvement_id (+) 
     and     io.involvement_form_outcome_id = 
             iof.involvement_form_outcome_id (+)
     and     ifm.description = 'Midnight League' and i.involvement_id  
             = '77176' )iv
     SET      iv.outcome_code = 'ENI'

Did you know that Stackoverflow is searchable database? When I use the `search` box at the top middle of the page and enter `ora-01779` I see there are 71 other Q/As about this already. Maybe you should look thru some of them. Good luck. — shellter, Jan 15 '18 at 13:35

William Robertson · Answer 1 · 2018-01-15T16:33:30.293

A key-preserved table in a view is one that only joins to unique keys. (In this case your update (select ...) uses an inline view, but the same rules apply.)

For example, if a view joins EMPLOYEES to DEPARTMENTS on deptno, then departments.deptno must be guaranteed unique by a primary or unique key or unique index. If it is, then Oracle knows that the join cannot introduce duplicates, and you can safely update columns of the view that belong to EMPLOYEES.

If departments.deptno was not guaranteed to be unique, then the view might include the same employee row twice. What should happen if you tried to update one and not the other? This is the situation that the error is preventing.

Note that Oracle only checks for the existence of unique constraints/indexes, regardless of whether or not any actual duplicates exist.

You might try turning it into a MERGE, which can be a little more flexible. This doesn't check for constraints and only fails on actual duplicates, giving ORA-30926: unable to get a stable set of rows in the source tables.

Update script errors

1 Answers1