Thanks for the question, Sam.

Asked: January 13, 2002 - 1:20 pm UTC

Last updated: April 28, 2011 - 7:51 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

"Data may be inserted into complex cviews when the statement affects only one of the tables in the join."

Tom, can you show us how in a view , the statement can effect only one of the tables in the join.

Does it mean that the insert can be done only into one table at a time in the view.

2."In complex views, for update statements all columns changed are extracted from a key -preserved table . In addition , if the view is created wiht the check option clause, join columns and columns taken from the tables that are referenced more than once in the view are not part of the update."

-What is a key preserved table.
-why are join columns not part of the update

3."In complex views, for delete statements, there is only one key preserved table in the join.this table may be present more than once in the join , unless the view has been created witht the check option."

can you explain the above...

4.why should all the columns where values are inserted must come from a key preserved table in case of of a complex view.

and Tom said...

searching for

key preserved

on this site, i find:
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:548422757486 <code>

which explains the concept (and once you understand the concept, the rest is pretty easy)....

1) so, here is the example of the insert:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t1 ( t1_x int primary key, t1_y date );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t2 ( t2_x int primary key, t2_y date );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace view v
2 as
3 select * from t1, t2 where t1.t1_x = t2.t2_x;

View created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into v values ( 1, sysdate, 1, sysdate );
insert into v values ( 1, sysdate, 1, sysdate )
*
ERROR at line 1:
ORA-01776: cannot modify more than one base table through a join view

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into v (t1_x, t1_y ) values ( 1, sysdate );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into v (t2_x, t2_y ) values ( 1, sysdate );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>

you see -- we cannot insert into both t1 and t2 using this view WITHOUT HELP (we can write an instead of trigger and provide the logic to insert into both underlying tables). We can directly insert into either of t1 or t2 as demonstrated however.

2) See the above link for "what is a key preserved..."

As for the part about the join columns -- well, only one table at a time can be modified via the view, and if you update the join columns -- that row would not appear in that view anymore (you changed the join column values -- that row doesn't exist anymore, the check condition fails). Hence in a view with check option the join columns cannot be modified. You can see this via:

ops$tkyte@ORA817DEV.US.ORACLE.COM> select column_name, updatable, insertable, deletable
2 from user_updatable_columns where table_name = 'V'
3 /

COLUMN_NAME UPD INS DEL
------------------------------ --- --- ---
T1_X YES YES YES
T1_Y YES YES YES
T2_X YES YES YES
T2_Y YES YES YES

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace view v
2 as
3 select * from t1, t2 where t1.t1_x = t2.t2_x
4 WITH CHECK OPTION;

View created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select column_name, updatable, insertable, deletable
2 from user_updatable_columns where table_name = 'V'
3 /

COLUMN_NAME UPD INS DEL
------------------------------ --- --- ---
T1_X NO NO NO
T1_Y YES YES YES
T2_X NO NO NO
T2_Y YES YES YES

All we did was recreate the view with check option here....

3) if there are two key preserved tables in a view (our example has 2 key preserved tables), we cannot delete from it cause Oracle doesn't know WHICH table to delete from and it won't do both.

ops$tkyte@ORA817DEV.US.ORACLE.COM> delete from v;
delete from v
*
ERROR at line 1:
ORA-01752: cannot delete from view without exactly one key-preserved table

Now, if we drop the primary key constraint:

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t1 drop constraint SYS_C004852 ;

Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> delete from v;

1 row deleted.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t2;

T2_X T2_Y
---------- ---------
1 13-JAN-02

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t1;

no rows selected

T1 remains key preserved but T2 is no longer key preserved. Hence we can delete from the view and the act of deleting from the view will delete from T1.

4) hopefully, once you read about key preserved -- this will be obvious.... We need to make sure that the row will appear at most "once" in the view itself -- otherwise the very act if modifying the row is ambigous....

Rating

(8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, January 14, 2002 - 1:22 am UTC

in this case why we don't use insted_of trigger

January 14, 2002 - 7:38 am UTC

go for it. never said you couldn't -- only that you would HAVE to if you wanted it to be fully insertable, updatable, deletable.

I did say:

...
you see -- we cannot insert into both t1 and t2 using this view WITHOUT HELP (we
can write an instead of trigger and provide the logic to insert into both
underlying tables). We can directly insert into either of t1 or t2 as
demonstrated however.
.....

Anaother updatable view related question

Oleksandr Alesinskyy, January 16, 2004 - 4:36 pm UTC

Hi Tom,

Is there a way programmaticaly (from data dictionary or in some other way) to determine if a given view is updatable?
USER_UPDATABLE_COLUMNS is not very useful as "SQL Reference" stated "To learn whether and in what ways the columns of an inherently updatable view can be modified, query the USER_UPDATABLE_COLUMNS data dictionary view. (The
information displayed by this view is meaningful only for inherently updatable views.)". So it is needed to know in advance is a view inherently updatable or not. Definitely, it is possible to try update and catch an exception, but possibly more elegant solution exists?

January 17, 2004 - 1:16 am UTC

either the view is inherently updatable ( and user_updatable_columns has Y for the columns that are)

OR

the view has an instead of trigger on it (and would be explicitly updatable).

so, look in those two -- if there is a trigger for the event you are interested in (update, insert, delete) OR the column(s) you are interested in have Y in the other view -- there you go.

Thanks so much !!!

Deepak, August 03, 2004 - 3:56 pm UTC

Hi Tom,

Thanks for explaining the concept Key-Preserved Table and Updatable Views.
Please help me understand one last thing regarding updatable views. The Oracle Concept manual says:
***All updatable columns of a join view must map to columns of a key-preserved table. If the view is defined with the WITH CHECK OPTION clause, then all join columns and "all columns of repeated tables" are non-updatable.****

Please explain what does "ALL COLUMNS OF REPEATED TABLES" mean in this statement.

Thanks
Deepak

August 03, 2004 - 5:55 pm UTC

repeated tables = tables appearing more than once in the from clause....

consider:

ops$tkyte@ORA9IR2> create table emp as select * from scott.emp;
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table emp add constraint emp_pk primary key(empno);
Table altered.
 
ops$tkyte@ORA9IR2> alter table emp add constraint emp_fk foreign key(mgr) references emp(empno);
Table altered.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace view v
  2  as
  3  select a.ename, b.ename mgr_name
  4    from emp a, emp b
  5   where a.mgr = b.empno
  6  /
 
View created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> update v set ename = ename;
 
13 rows updated.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace view v
  2  as
  3  select a.ename, b.ename mgr_name
  4    from emp a, emp b
  5   where a.mgr = b.empno<b>
  6    WITH CHECK OPTION</b>
  7  /
 
View created.
 
ops$tkyte@ORA9IR2> update v set ename = ename;
update v set ename = ename
             *
ERROR at line 1:
ORA-01733: virtual column not allowed here
 
<b>and now you cannot update</b>

ALL COLUMNS OF REPEATED TABLES

Deepak, August 04, 2004 - 9:53 am UTC

Hi Tom,

I still did not get the rational behind on why this update is not allowed.

-----------------------------
ops$tkyte@ORA9IR2> create or replace view v
2 as
3 select a.ename, b.ename mgr_name
4 from emp a, emp b
5 where a.mgr = b.empno
6 WITH CHECK OPTION
7 /

ops$tkyte@ORA9IR2> update v set ename = ename;
update v set ename = ename
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
---------------------------------

In the view v, Emp A is Key-Preserved Table which assures oracle that the Key for Emp A is also the key to View V. An update statement without the WITH CHECK OPTION shows that.

The update statement is not on the join columns implying that if another select is made, oracle can pull same set of records again.

By not allowing the update statement in case of WITH CHECK OPTION for repeated tables, does Oracle violates the definition of WITH CHECK OPTION ?

Thanks
Deepak

August 04, 2004 - 10:58 am UTC

how could not allowing the update violate the "with check option"??!? if you don't modify it, it of course still satisfies the check option by definition.

with the standard 14 rows of EMP, consider this example:

create or replace view v
as
select a.ename, b.ename mgr_name
from emp a, emp b
where a.mgr = b.empno
and b.ename like '%S%'
/

select * from v;
update v set ename = 'FOO' where ename = 'SCOTT';
select * from v;

think about what happens with that.

ALL COLUMNS OF REPEATED TABLES

Deepak, August 04, 2004 - 12:34 pm UTC

Hi Tom,

Per definition,
"WITH CHECK OPTION creates the view with the constraint that
INSERT and UPDATE statements issued against the view are not allowed to create or result in rows that the view cannot select".

The view you just mentioned does fall into the definition of WITH CHECK OPTION. So, when this view is defined with the WITH CHECK OPTION Oracle would not allow the update as the view cannot select same rows again.

However, consider this view:

A) create or replace view v
as
select a.ename, b.job mgr_title
from emp a, emp b
where a.mgr = b.empno ;

Update on Ename is Allowed.

B) create or replace view v
as
select a.ename, b.job mgr_title
from emp a, emp b
where a.mgr = b.empno
WITH CHECK OPTION;

Update on ename is not allowed.

Which implies it is the WITH CHECK OPTION which is blocking the Update on ename Statement.

If Oracle had allowed this update statement, what harm could it have done? A select from this View after Update would result in the same set of rows.

Thanks
Deepak

August 04, 2004 - 1:13 pm UTC

but how would Oracle be able to do that *efficiently*.

there is "technically feasible"
and
there is "realistic"

Join updates

A reader, May 04, 2005 - 1:50 pm UTC

Hi Tom,

I have two tables

create table t1
(
id number,
vvv varchar2(20)
);

create table t2
(
id number,
yyy varchar2(20)
);

I have created a view v1 as

create or replace view v1 as
select t1.id, t2.yyy from t1, t2
where t1.id = t2.id;

Now when I try to update v1 I am getting ora-01776: cannot modify more than one base table using a join view.

To avoid this, should I directly do the updates on the tables t1 and t2.

For example if I have
update v1 set id = 2 where id = 1;

I should change it as

update t1 set id = 2 where id = 1;
update t2 set id = 2 where id = 1;

Please comment.

Thanks.

May 04, 2005 - 1:58 pm UTC

well, you do see the problem here -- you can in fact update v set id = 2 where id = 1:



ops$tkyte@ORA9IR2> create table t1
  2  (
  3  id number,
  4  vvv varchar2(20)
  5  );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t2
  2  (
  3  id number PRIMARY KEY,
  4  yyy varchar2(20)
  5  );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace view v1 as
  2  select t1.id, t2.yyy from t1, t2
  3  where t1.id = t2.id;
 
View created.
 
ops$tkyte@ORA9IR2> insert into t1 values ( 1, null );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t2 values ( 1, null );
 
1 row created.
 
ops$tkyte@ORA9IR2> update v1 set id = 2 where id = 1;
 
1 row updated.
 
ops$tkyte@ORA9IR2>


but the problem is that only updates a single table and you appear to want to do both.

You could use an instead of trigger (not recommended) 
or update the base tables (my recommended approach)

followup

A reader, May 04, 2005 - 3:06 pm UTC

Hi Tom,

I can go with your recommended approach (to update the base tables directly). I have one doubt. How are you telling that it appears that I want to update both the tables rather than one table?

Thanks.

May 04, 2005 - 3:19 pm UTC

<quote>
I should change it as

update t1 set id = 2 where id = 1;
update t2 set id = 2 where id = 1;
</quote>

Really good

Bishnu Barik, April 28, 2011 - 4:34 am UTC

Dear sir,
exactly i have searched maximum sites for insert into complex view but this sites help me better then others.I really thanks to u & your team .

April 28, 2011 - 7:51 am UTC

hah, team, yeah - team of one.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Anaother updatable view related question

Thanks so much !!!

ALL COLUMNS OF REPEATED TABLES

ALL COLUMNS OF REPEATED TABLES

Join updates

followup

Really good

More to Explore

PL/SQL demos

PL/SQL docs