Page 1 of 1

update row fails with error ORA-01779

PostPosted: Mon Oct 22, 2018 2:05 pm
by JuFi
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

Re: update row fails with error ORA-01779

PostPosted: Mon Oct 22, 2018 2:29 pm
by rzenz
Yes, setting a SQL-String as Writeback-Table will not allow you to run an update against it. Example:

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=?;

Re: update row fails with error ORA-01779

PostPosted: Mon Oct 22, 2018 4:46 pm
by ANH
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

Re: update row fails with error ORA-01779

PostPosted: Mon Oct 22, 2018 9:28 pm
by Support@SIB
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

Re: update row fails with error ORA-01779

PostPosted: Tue Oct 23, 2018 8:58 am
by ANH
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

Re: update row fails with error ORA-01779

PostPosted: Tue Oct 23, 2018 9:17 am
by rzenz
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?