ORA-01779: Error [message #394289] |
Thu, 26 March 2009 06:56  |
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   |
cookiemonster
Messages: 13963 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   |
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 #394303 is a reply to message #394296] |
Thu, 26 March 2009 07:28   |
pablolee
Messages: 2882 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 #394306 is a reply to message #394289] |
Thu, 26 March 2009 07:34   |
cookiemonster
Messages: 13963 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   |
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   |
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   |
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  |
flyboy
Messages: 1903 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).
|
|
|