update row in a view
Thomas Meiers
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
mark...@my-deja.com
the view must create what is called a key preserved table and you must
be on version 7.3 or higher.
Basically a key preserved table is a result set where one of the column
values in the result set uniquely identifies one row and only one row
in the target table of the DML statement. Using the emp and dept
tables as an example if the view returns the empno (employee number)
which is the unique key to the emp table and also returns only one row
per empno then you should be able to use this view to perform DML. You
can look at the dictionary table all_updatable_columns to see if you
can update via the view. Note the view changed between version 7 where
it just listed the view as updatable or not and ver 8 where it
specified whether or not you can insert, update, and delete per
column. Also I prefer to join to this view to all_views to filter out
tables.
Naturally there are restrictions on the view having group by, distinct,
nested tables, etc....
For ver 8 ch 13 of the DBA Admin manual covers the topic pretty well.
--
Mark D. Powell -- The only advice that counts is the advice that
you follow so follow your own advice --
Sent via Deja.com http://www.deja.com/
Before you buy.
Thomas Kyte
(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@8i> create table t1 ( x int, a int );
Table created.
ops$tkyte@8i> create table t2 ( y int, b int );
Table created.
ops$tkyte@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@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@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@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@8i> alter table t1 add constraint t1_pk primary key(x)
2 /
Table altered.
ops$tkyte@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@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@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@8i> alter table t2 add constraint t2_pk primary key(y)
2 /
Table altered.
ops$tkyte@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@8i> update v set x = y;
0 rows updated.
ops$tkyte@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@8i> alter table t1 drop constraint t1_pk;
Table altered.
ops$tkyte@8i> alter table t2 drop constraint t2_pk;
Table altered.
ops$tkyte@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@8i> update v set x = y, y = x;
0 rows updated.
ops$tkyte@8i> update v set x = y;
0 rows updated.
ops$tkyte@8i> update v set y = x;
0 rows updated.
==================================================================
ops$tkyte@8i> insert into t1 values ( 1, 1 );
ops$tkyte@8i> insert into t1 values ( 1, 2 );
ops$tkyte@8i> insert into t2 values ( 1, null );
ops$tkyte@8i> update v set b = a;
2 rows updated.
ops$tkyte@8i> select * from v;
X A Y B
---------- ---------- ---------- ----------
1 2 1 2
1 2 1 2
ops$tkyte@8i> rollback;
Rollback complete.
ops$tkyte@8i> insert into t1 values ( 1, 2 );
ops$tkyte@8i> insert into t1 values ( 1, 1 );
ops$tkyte@8i> insert into t2 values ( 1, null );
ops$tkyte@8i> update v set b = a;
2 rows updated.
ops$tkyte@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 tk...@us.oracle.com
Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Thomas Meiers
SKurosky
>
>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
>
>
>
>
>
>
I don't think you can update a view with a join. Only single table views can be
updated, I believe
Hth
Sandy
Keith Jamieson
upon which the view is based. Since the views are dynamic, they
automatically reflect the updated data.
SKurosky wrote in message <20000120141305...@ng-cm1.aol.com>...
SKurosky
view.
Sandy
>
>
>
>
>
>
Igor V. Podolsky
news:20000121132104...@ng-fs1.aol.com...
> >My understanding is that you dont update the view. You update the tables
> >upon which the view is based. Since the views are dynamic, they
> >automatically reflect the updated data.
Sure, but You can update underlaying tables using 'update <view_name>'
statement. But there are some restrictions: documentation says something
about 'key-preserved tables. It's quite complex thing, but in the following
example You'd just check existance of primary (may be unique key is enough)
key on t1.c2 and foreign key on t2.c2. The background of restrictions is
simple - Oracle have to know what rows and columns of underlaying table
should be updated.
> >SKurosky wrote in message
<20000120141305...@ng-cm1.aol.com>...
> >>>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?
> >I didn't ask the question. I replied to the question regarding updating a
> view.
:)
--
Is There A God Or Any Kind Of Justice Under The Sky... (Queen'91)
Igor V. Podolsky (igo...@soft-review.kiev.ua)