Re: Rules about Updateable Views

Martin Haltmayer <Martin.Haltmayer_at_d2mail.de> wrote in message news:<3B3F7A58.2F995F53_at_d2mail.de>...
> 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

To add to what Martin said, there is dictionary view that will tell you if a view can be used to perform updates, inserts, and deletes against specific columns in the view:

set echo off
rem
rem SQL*Plus script to display update DML status of view columns rem
rem 19990413 Mark D Powell New Script, ver. 8 rem
set pagesize 55
set verify off
column owner format a12

accept view_nm prompt 'Enter the view name to be checked: ' prompt This query will return "no rows" if entered value is not a view

select uc.owner,

       uc.column_name, uc.updatable, uc.insertable, uc.deletable from all_updatable_columns uc,

       all_views v

where  uc.table_name = upper('&view_nm')
and    uc.table_name = v.view_name
and    uc.owner      = v.owner

• Mark D Powell --