Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01779: Error (Oracle 10.2.0.1.0)
ORA-01779: Error [message #394289] Thu, 26 March 2009 06:56 Go to next message
mbmalasenthil
Messages: 27
Registered: July 2008
Junior Member
Friends,

I am trying to update a complex view. When I update the view it says "ORA-01779: cannot modify a column which maps to a non key-preserved table". I have shown the date in the tables and the update statement below.

SQL> select * from table1;

     EMPNO ENAME                                                                
---------- --------------------                                                 
         1 Lara                                                              
         2 Murali                                                                 

SQL> select * from table2;

    DEPTNO DNAME                                                                
---------- --------------------                                                 
         1 Botany                                                               
         2 Physics                                                              

SQL> select * from table3;

    SUBJNO SNAME                                                                
---------- --------------------                                                 
         1 Anatomy                                                              
         2 Light                                                                

SQL> create or replace view sen_subj as
  2  select empno, ename, sname from table1, table3
  3  where
  4  exists(select deptno from table2 where
  5                empno=deptno and deptno=subjno)
  6  ;

View created.

SQL> update sen_subj
  2  set empno=100 where empno=1;
set empno=100 where empno=1
    *
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table 



I have no proper/clear understanding of the error. I tried resolving this problem using INSTEAD OF trigger. Please suggest me whether there are any ways to solve the problem. Any help would be appreciated. Thanks very much!

Senthil
Re: ORA-01779: Error [message #394293 is a reply to message #394289] Thu, 26 March 2009 07:04 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Forget the error message, the view query makes no sense.
table1 and table3 are basically cartesian joined.
the subquery makes no sense - how can deptno be equal to empno and subjno? I know it works with your test data but in general it makes no sense.

Fix these issues and your key mapping error may well go away at the same time.
Re: ORA-01779: Error [message #394296 is a reply to message #394293] Thu, 26 March 2009 07:09 Go to previous messageGo to next message
mbmalasenthil
Messages: 27
Registered: July 2008
Junior Member
cookiemonster, thats just an example.. Assuming that in reality i have query of that kind on some tables is there a way to update the underlying base tables? I understand that the example i quoted there is meaningless. Thnx.
Re: ORA-01779: Error [message #394300 is a reply to message #394296] Thu, 26 March 2009 07:23 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Try to see if this link helps


http://forums.oracle.com/forums/thread.jspa?threadID=378144

[Updated on: Thu, 26 March 2009 07:24]

Report message to a moderator

Re: ORA-01779: Error [message #394303 is a reply to message #394296] Thu, 26 March 2009 07:28 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Quote:
I understand that the example i quoted there is meaningless.

I your car broke down, would you take your bycicle to the mechanics and ask them to fix your car?
Please Post a valid test case i.e. data which represents, accurately, the state of your data.
Re: ORA-01779: Error [message #394305 is a reply to message #394296] Thu, 26 March 2009 07:32 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
mbmalasenthil wrote on Thu, 26 March 2009 13:09
cookiemonster, thats just an example.. Assuming that in reality i have query of that kind on some tables is there a way to update the underlying base tables? I understand that the example i quoted there is meaningless. Thnx.

There is nice explanation of this error on AskTom: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:548422757486.
Or in other words further in that thread: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:548422757486#45348173246731.
Or, in another thread: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:273215737113#3261480541599.

[Edit: Added third link]

[Updated on: Thu, 26 March 2009 07:33]

Report message to a moderator

Re: ORA-01779: Error [message #394306 is a reply to message #394289] Thu, 26 March 2009 07:34 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Have you tried the documentation?

mbmalasenthil wrote

Assuming that in reality i have query of that kind on some tables is there a way to update the underlying base tables?



Since you've said that your example is meaningless how are supposed to know what that kind is?

Re: ORA-01779: Error [message #394311 is a reply to message #394289] Thu, 26 March 2009 07:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This should do the job.
What problem did you have with the Instead of trigger - it works for me.
Just run this in sql*plus and you should see what's happening:
drop table test_157;
drop table test_158;
drop table test_159;

create table test_157 (empno  number, ename  varchar2(50));
create table test_158 (deptno number, dname  varchar2(50));
create table test_159 (subjno number, sname  varchar2(50));

insert into test_157 values (1 ,'Lara');      
insert into test_157 values (2 ,'Murali');

insert into test_158 values (1 ,'Botany');
insert into test_158 values (2 ,'Physics');

insert into test_159 values (1 ,'Anatomy');
insert into test_159 values (2 ,'Light');

create or replace view sen_subj as
  select empno, ename, sname 
  from   test_157,test_159
  where exists(select deptno 
               from   test_158
               where  empno=deptno 
               and    deptno=subjno);
               
create or replace trigger sen_subj_upd
instead of update on sen_subj
begin
  update test_157 
  set    empno=:new.empno
  where  empno=:old.empno;
  
  update test_158
  set    deptno=:new.empno
  where  deptno=:old.empno;
  
  update test_159 
  set    subjno=:new.empno
  where  subjno=:old.empno;  
end;
/

select * from sen_subj;  

update sen_subj
set empno=100 where empno=1;

select * from sen_subj;  

select * from test_157;
select * from test_158;
select * from test_159;
Re: ORA-01779: Error [message #394322 is a reply to message #394311] Thu, 26 March 2009 08:08 Go to previous messageGo to next message
mbmalasenthil
Messages: 27
Registered: July 2008
Junior Member
Thanks very much JRowbottom. Its working alright with INSTEAD OF triggers. I was trying to find whether we can solve the issue without using INSTEAD OF triggers. In other words, I am trying to find from you friends whether this is the only alternative available for such problems?

Senthil
Re: ORA-01779: Error [message #394358 is a reply to message #394322] Thu, 26 March 2009 11:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There is a way of bypassing the ORA-01779 cases in some situations, but it won't work for you.
Your problem isn't that you got the ORA-1779, it's that you want an update of your view to update columns in 3 seperate tables.

For that, as far as I know, you're going to need an INSTEAD OF trigger.

It's conceivable that if you did bypas the ORA-1779, you might be able to have an update trigger on table1 (in your example) that would update table2 and table3, but that would be such a bad solution that I'm not going to go into any further details.

INSTEAD OF triggers were created to handle DML on complex views. as this is what you're doing, I would need a very strong motivation to look any further.
Re: ORA-01779: Error [message #394362 is a reply to message #394322] Thu, 26 March 2009 11:32 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
mbmalasenthil wrote on Thu, 26 March 2009 14:08
Thanks very much JRowbottom. Its working alright with INSTEAD OF triggers. I was trying to find whether we can solve the issue without using INSTEAD OF triggers. In other words, I am trying to find from you friends whether this is the only alternative available for such problems?

Senthil

Firstly, you shall understand, why are you getting this problem. If you would read the links I provided and did a simple SELECT from that view, you would realize that you are trying to update one row in TABLE1 twice.

Either restrict it to join at most one row from TABLE3 for each row in TABLE1 (as there is cartesian product, it seems impossible), or update directly TABLE1, e.g.
UPDATE table1
SET empno=100
WHERE empno=1
  AND EXISTS(SELECT deptno
             FROM table2, table3
             WHERE empno=deptno
               AND deptno=subjno);
(this posted example is really weird).
Previous Topic: Return all errors on a screen from exception
Next Topic: Deployment Guides
Goto Forum:
  


Current Time: Sat Dec 10 10:40:48 CST 2016

Total time taken to generate the page: 0.08053 seconds