Re: Deleting from Vievs

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 29 Dec 1999 22:40:59 -0000
Message-ID: <946507555.7928.0.nnrp-14.9e984b29_at_news.demon.co.uk>

In 7.3+ you can delete from a join view provided it contains exactly one key-preserved table, although the definition of 'key-preserved' isn't immediately obvious, especially if you read the manuals. - e.g.

create table t_1 (n1 number primary key, v1 varchar2(20)); create table t_2 (n1 number, n2 number , v1 varchar2(20));

create view v_1 as
select t1.n1 t1_n1, t2.n2 t2_n2, t1.v1 t1_v1, t2.v1 t2_v1 from t_1 t1,t_2 t2
where t1.n1 = t2.n1
;

View created.

desc v_1

 Name                            Null?    Type
 ------------------------------- -------- ----
 T1_N1                                    NUMBER
 T2_N2                                    NUMBER
 T1_V1                                    VARCHAR2(20)
 T2_V1                                    VARCHAR2(20)


insert into t_1 values (1,'a');

insert into t_2 values (1,1,'x');
insert into t_2 values (1,2,'y');
commit;

select * from v_1;

    T1_N1     T2_N2 T1_V1                T2_V1
--------- --------- -------------------- --------------------
        1         1 a                    x
        1         2 a                    y

delete from v_1 where t2_n2 = 2;

1 row deleted.

select * from v_1;

    T1_N1     T2_N2 T1_V1                T2_V1
--------- --------- -------------------- --------------------
        1         1 a                    x

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

david_g wrote in message <05108cf7.9323360b_at_usw-ex0107-049.remarq.com>...


>Assuming the permissions allow, you can only delete from a view that is


>based upon one table.

>

>

>* Sent from RemarQ http://www.remarq.com The Internet's Discussion Network

*


>The fastest and easiest way to search and participate in Usenet - Free!


>

Received on Wed Dec 29 1999 - 23:40:59 CET