Re: Rules about Updateable Views

Dear Dino,

I guess the problem is the idea of "key-preserved". The abstract definition is: a table in a join is key-preserved if any key in the original table will also be a key in the join (if you consider the join to be the new table).

Consider the following example:

create table my_tab1 (pk number, f1 number, f2 number, f3 number, fldx varchar2(20));

create table my_tab2 (pk number, xyz number);

create unique index my_tab2_pk on my_tab2 (pk);

create table my_tab3 (pk number, foo number, bar number);

create unique index my_tab3_pk on my_tab3 (pk);

Imagine you want to update my_tab1. The new value for f1 should be the lookup of xyz by pk in my_tab2, the new value for f2 should be the lookup of foo by pk in my_tab3 and f3 should become the lookup of bar by pk in my_tab3. You could this like

update my_tab1 set
f1 = (select xyz from my_tab2 where my_tab1.pk=my_tab2.pk), (f2,f3) = (select foo,bar from my_tab3 where my_tab1.pk=my_tab3.pk) where my_tab1.fldx='Anything'
/

If you have the above mentioned unique indexes the join between my_tab1, my_tab2 and my_tab3 is key-preserved and you can code the update as

update (

select	t1.f1
	, t1.f2
	, t1.f3
	, t2.xyz
	, t3.foo
	, t3.bar
from	my_tab1 t1
	, my_tab2 t2
	, my_tab3 t3
where	1 = 1
and	t1.pk = t2.pk
and	t1.pk = t3.pk
and	t1.fldx = 'Anything'
) x
set	x.f1 = x.xyz
	, x.f2 = x.foo
	, x.f3 = x.bar

Martin

Dino Hsu wrote:
>
> Dear all,
>
> In a book, it says about updateable views:
> 1.There are two types of views: simple and complex.
> 1-1.Simple views are those that have only one underlying table.
> 1-2.Complex views are those with two or more underlying tables that
> have been joined together.
> 2.Data may be inserted into simple views except in the following
> cases:
> 2-1.If the with check option is used, the user may not insert, delete,
> or update data on the table underlying the simple view if the view
> itself is not able to select that data for the user.
> 2-2.The user may not insert, delete, or update data on the table
> underlying the simple view if the select statement creating the view
> contains group by, order by, or a single-row operation.
> 2-3.No data may be inserted in simple views that contain references to
> any virtual column, such as ROWID, CURRVAL, NEXTVAL, and ROWNUM.
> 2-4.No data may be inserted into simple views that are created with
> the read only option.
> 3.Data may be inserted into complex views when all of the following
> conditions are true:
> 3-1.The statement affects only one of the tables in the join.
> 3-2.For update statements, all columns changed are extracted from a
> key-preserved table. In addition, if the view is created with the with
> check option clause, join columns and columns taken from tables that
> are referenced more thatn once in the view are not part of the update.
> 3-3.For delete statments, 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 with the with check option clause.
> 3-4.For insert statements, all columns where values are inserted must
> come from a key-preserved table, and the view must not have been
> created with the with check option cluase.
>
> I find this hard to understand, anyone can interpret it? Thanks in
> advance.
>
> Dino
  Received on Sun Jul 01 2001 - 14:30:32 CDT