Re: update row in a view

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 10 Jan 2000 11:56:17 -0500
Message-ID: <d03k7ssf3fnjnpfjei24kk1kckk0eeackh@4ax.com>

A copy of this was sent to Thomas Meiers <ixes_at_gmx.de > (if that email address didn't require changing) On Mon, 10 Jan 2000 17:04:10 +0100, you wrote:

>Hallo!
>
>I have a view like
>select t1.c1 t2.c1 from t1,t2 where t1.c2 = t2.c2.
>I canīt update rows in this view.
>What did i wrong?
>
>Any help would be great!
>
>Thomas Meiers

It'll depend on what kind of unique constraints/primary keys you have in place. Consider this example:

ops$tkyte_at_8i> create table t1 ( x int, a int ); Table created.

ops$tkyte_at_8i> create table t2 ( y int, b int ); Table created.

ops$tkyte_at_8i> create view v
  2 as select * from t1, t2 where t1.x = t2.y   3 /
View created.

The following shows that we cannot update either of the base tables T1 or T2 since there are NO keys in place used by our view to join the tables:

ops$tkyte_at_8i> update v set x = y, y = x; update v set x = y, y = x

             *
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table

ops$tkyte_at_8i> update v set x = y;
update v set x = y

             *
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table

ops$tkyte_at_8i> update v set y = x;
update v set y = x

             *
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table

So, here we add a key to T1. This will allow us to update T2 (surprise), but not T1. That is because the database knows that each row in T2 will be joined to at MOST one row in T1 (since the thing we join T2 to T1 by is T1's key). There are no cartesian products to concern ourselves with, no ambiguities -- the outcome of the update is deterministic:

ops$tkyte_at_8i> alter table t1 add constraint t1_pk primary key(x)   2 /

Table altered.

ops$tkyte_at_8i> update v set x = y, y = x; update v set x = y, y = x

             *
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table

ops$tkyte_at_8i> update v set x = y;
update v set x = y

             *
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table

ops$tkyte_at_8i> update v set y = x;

0 rows updated.

Now, we add another key to T2. This'll let us update T1 and T2 -- but not at the same time:

ops$tkyte_at_8i> alter table t2 add constraint t2_pk primary key(y)   2 /

Table altered.

ops$tkyte_at_8i> update v set x = y, y = x; update v set x = y, y = x

                    *

ERROR at line 1:
ORA-01776: cannot modify more than one base table through a join view

ops$tkyte_at_8i> update v set x = y;

0 rows updated.

ops$tkyte_at_8i> update v set y = x;

0 rows updated.

Going one step further, in Oracle8.0 and up we can create an instead of trigger to 'train' our view how to do the updates -- regardless of the keys in place. Here we can get rid of the keys and direct the updates to do the 'right' thing. Warning: watch your logic here -- you can code it such that the outcome is non-deterministic, that is, running the same update against the same data results in DIFFERENT outcomes. short example below the zero row updates....

ops$tkyte_at_8i> alter table t1 drop constraint t1_pk;

Table altered.

ops$tkyte_at_8i> alter table t2 drop constraint t2_pk;

Table altered.

ops$tkyte_at_8i> create or replace trigger v_trigger   2 instead of update on V
  3 begin

  4          update t1 set x = :new.x, a = :new.a where x = :old.x;
  5          update t2 set y = :new.y, b = :new.b where y = :old.y;

  6 end;
  7 /

Trigger created.

ops$tkyte_at_8i> update v set x = y, y = x;

0 rows updated.

ops$tkyte_at_8i> update v set x = y;

0 rows updated.

ops$tkyte_at_8i> update v set y = x;

0 rows updated.

---

ops$tkyte_at_8i> insert into t1 values ( 1, 1 );
ops$tkyte_at_8i> insert into t1 values ( 1, 2 );
ops$tkyte_at_8i> insert into t2 values ( 1, null );

ops$tkyte_at_8i> update v set b = a;
2 rows updated.

ops$tkyte_at_8i> select * from v;

         X A Y B ---------- ---------- ---------- ----------

         1          2          1          2
         1          2          1          2

ops$tkyte_at_8i> rollback;
Rollback complete.

ops$tkyte_at_8i> insert into t1 values ( 1, 2 );
ops$tkyte_at_8i> insert into t1 values ( 1, 1 );
ops$tkyte_at_8i> insert into t2 values ( 1, null );

ops$tkyte_at_8i> update v set b = a;
2 rows updated.

ops$tkyte_at_8i> select * from v;

         X A Y B ---------- ---------- ---------- ----------

         1          1          1          1
         1          1          1          1

see -- same data -- different answsers. thats because with the lack of keys on the table, the update is totally ambigous

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Jan 10 2000 - 10:56:17 CST