[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-04-20
5
Medium Priority
?
1,326 Views
Last Modified: 2012-06-21
this update stm returns
ORA-01779: cannot modify a column which maps to a non key-preserved table


UPDATE   (SELECT   pc.subject_number,
                   pc.device,
                   pc.cond_start_date,
                   pc.nonmobile_START_DATE,
                   pc.nonmobile_end_date                  
            FROM      myschema.aud_show_fin pc
                   INNER JOIN
                      myschema.aud_subject P
                   ON P.subject_NUMBER = pc.subject_number
           WHERE   p.provider_type_code NOT IN ('060', '052') AND pc.cond_type IN ('0C', 'OD')) t
   SET   t.device =
            (SELECT   devicetype_id
               FROM   myschema.dim_devicetype
              WHERE   Description = 'Land Line'),
         t.nonmobile_start_date = t.cond_start_date,
         t.nonmobile_end_date = TO_DATE ('12/31/9999', 'mm/dd/yyyy');


please advise
0
Comment
Question by:it-rex
  • 3
5 Comments
 
LVL 14

Assisted Solution

by:ajexpert
ajexpert earned 800 total points
ID: 35436925
check if this links helps you

http://psoug.org/forum/index.php?topic=65.0

0
 
LVL 11

Author Comment

by:it-rex
ID: 35437008
it did not
0
 
LVL 2

Accepted Solution

by:
kambleamar earned 1200 total points
ID: 35438798
let me try to help you, but please read the example text below

create table m (id number primary key, col number);
insert into m values(1,1);
create table c (id number primary key, fk_m number references m, col number);
insert into c values(1,1,1);
create or replace view v as select m.id, m.col m_col, c.col c_col from m left join c on m.id = c.fk_m;

select * from v;

ID M_COL C_COL

--------------------------------------------------------------------------------
----------
--------------------------------------------------------------------------------
 1 1 1

so far, so good. now an update:

update v set m_col = 2 where id=1;

now here
m.id cannot be a PK of the view because there could be more than one row in c pointing to the same row in m. Table c is key preserved (thus updateable) in the inner join version because each row of c can only appear once in the view.

For a table to be key preserved in a view, the primary key of that table, if it were present in the view, ciould serve as a primary key of the view.

In here case, m.id cannot be a PK of the view because there could be more than one row in c pointing to the same row in m. Table c is key preserved (thus updateable) in the inner join version because each row of c can only appear once in the view.



0
 
LVL 11

Assisted Solution

by:it-rex
it-rex earned 0 total points
ID: 35440379
so in my example
which piece of code corresponds to the view with PK?
0
 
LVL 11

Author Closing Comment

by:it-rex
ID: 35465190
Thanks again
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses
Course of the Month20 days, 3 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question