update row fails with error ORA-01779
6 posts
• Page 1 of 1
update row fails with error ORA-01779
Hello,
I've set a sql-string as a writebacktable that selects from different db tables.
Now I am trying to update a row and I get the error:
ORA-01779: cannot modify a column which maps to a non key-preserved table
How can I achieve an update in this case?
Thank you.
Regards,
Julia
I've set a sql-string as a writebacktable that selects from different db tables.
Now I am trying to update a row and I get the error:
ORA-01779: cannot modify a column which maps to a non key-preserved table
How can I achieve an update in this case?
Thank you.
Regards,
Julia
- JuFi
- Posts: 12
- Joined: Thu Oct 11, 2018 2:20 pm
Re: update row fails with error ORA-01779
Yes, setting a SQL-String as Writeback-Table will not allow you to run an update against it. Example:
Now when execute a select and an update on this storage the following queries will be executed on the database (with "a" being the primary key):
If we set a SQL-String as writeback table:
We get the following queries:
And we see that the update statement cannot work. What you want is to set the from clause and the writeback table to appropriate values:
Which will yield the expected results:
- Code: Select all
DBStorage storage = new DBStorage();
storage.setDBAccess(dbAccess);
storage.setWritebackTable("test");
storage.open();
Now when execute a select and an update on this storage the following queries will be executed on the database (with "a" being the primary key):
- Code: Select all
select m.a, m.b, m.c from test m;
update test set b=?, c=? where a=?;
If we set a SQL-String as writeback table:
- Code: Select all
DBStorage storage = new DBStorage();
storage.setDBAccess(dbAccess);
storage.setWritebackTable("(select * from test)");
storage.open();
We get the following queries:
- Code: Select all
select m.a, m.b, m.c from (select * from test) m;
update (select * from test) set b=?, c=? where a=?;
And we see that the update statement cannot work. What you want is to set the from clause and the writeback table to appropriate values:
- Code: Select all
DBStorage storage = new DBStorage();
storage.setDBAccess(dbAccess);
storage.setFromClause("(select * from test)");
storage.setWritebackTable("test");
storage.open();
Which will yield the expected results:
- Code: Select all
select m.a, m.b, m.c from (select * from test) m;
update test set b=?, c=? where a=?;
-
rzenz - Posts: 36
- Joined: Mon Dec 12, 2016 1:40 pm
- Location: Vienna, Austria
Re: update row fails with error ORA-01779
Thank you for your reply.
In our case we have two tables joint together like this
"select .... from table_a a, table_b b1, table_b b2 ..."
Following your advice we would use our select statement as the argument in a call of the setFromClause function.
But now we have difficulties to understand what the argument for the setWritebackTable should be.
My idea was to use a insteadOfUpdate event handler. There I wanted to update the two tables manually.
Is this posible to do this by using the already established db connection?
Thank you.
Best Regards
Andreas
In our case we have two tables joint together like this
"select .... from table_a a, table_b b1, table_b b2 ..."
Following your advice we would use our select statement as the argument in a call of the setFromClause function.
But now we have difficulties to understand what the argument for the setWritebackTable should be.
My idea was to use a insteadOfUpdate event handler. There I wanted to update the two tables manually.
Is this posible to do this by using the already established db connection?
Thank you.
Best Regards
Andreas
- ANH
- Posts: 11
- Joined: Fri Jun 29, 2018 11:42 am
Re: update row fails with error ORA-01779
Why do you update two tables?
But anyway, it's possible to use a view as writeback table and implement the instead of trigger directly in the database or, yes, it's also possible to use the eventInsteadOf... to do the task manually.
Here's a short info: InsteadOf Trigger
But anyway, it's possible to use a view as writeback table and implement the instead of trigger directly in the database or, yes, it's also possible to use the eventInsteadOf... to do the task manually.
Here's a short info: InsteadOf Trigger
-
Support@SIB - Posts: 353
- Joined: Mon Sep 28, 2009 1:56 pm
Re: update row fails with error ORA-01779
Why do you update two tables?
Because a user could have changed data from both tables.
Would you advice that we should avoid doing this? And if so, why?
Thank you!
Best regards,
Andreas
- ANH
- Posts: 11
- Joined: Fri Jun 29, 2018 11:42 am
Re: update row fails with error ORA-01779
I think what the question was how your data model looks like, how it operates and how the user is interacting with it. Am I assuming correctly that these tables, which are in your select statement, are in some form 1:1 relation? So that you have basically one row split out over multiple tables?
-
rzenz - Posts: 36
- Joined: Mon Dec 12, 2016 1:40 pm
- Location: Vienna, Austria
6 posts
• Page 1 of 1