Re: How to update through a joined view in 8i?

This was possible long before 8.1; the jargon is 'key-preserved tables'. I haven't tested 8.1 to see if there example works, but I have done this on occasion for bulk updates.

Creating an example off the cuff - I hope it works:

create table t1 (n1 number primary key, v1 varchar2(10)); create table t2 (n2 number, v2 varchar2(10));

insert into t1 values (1,'abc');
insert into t2 values (1,'xyz');

commit;

create view upd_view as

    select t1.v1, t2.v2
    from t1, t2
    where t1.n1 = t2.n2
;

update upd_view

    set v2 = v1;

I can update columns in T2 through this view, because the join to T1 cannot introduce multiple copies of a T2 row. On the other hand, a single row in T1 could join to multiple row in T2 so the T1 rows are not updateable.

--

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

CA wrote in message ...


>Chad,

>

>I appreciate your taking the time to respond, but this misses the point of

>my original question. The pre-insert triggers were availiable before Oracle

>8i.  Starting in 8i, Oracle has claimed that you can insert, update, and

>delete records directly through a JOINED view - without using triggers -

>under certain conditions.  The example they give for it, though, doesn't

>work.  If you've seen anyone use this ability, I'd appreciate hearing how

>they did it.

>

>

>"Ghost" <thomp901_at_micron.net> wrote in message

>news:39BB9BAE.6174D973_at_micron.net...

>>   I don't have my books here, but I think you use a pre-insert

>> trigger.  If you're trying to update Oracle Apps databases, you can

>> tie in the appropriate APIs at that point.  I've done it once just to

>> try it out, but not for production use.

>>

>> CA wrote:

>> >

>> > Anyone here successfully updated (or deleted or inserted) data through

 a


>> > joined view in Oracle 8i?  The online docs provide an example of how to

 do


>> > this, and the example itself doesn't work.  If anyone could provide a

 brief


>> > example of how to make this work, I'd sure appreciate it.

>>

>> --

>> ---------------------------------------------------------------------

>> Chad Thompson,

>> Programmer Analyst: VB, VC++, PLSQL, Oracle HRMS, Security Apps

>>                             Home: thomp901_at_micron.net

>>

>> Thou shalt not tick off the dragon.... for thou art crunchy and

>> taste good with ketchup.

>> ---------------------------------------------------------------------

>

>