Re: View problem - cannot update with a Join

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/09/07
Message-ID: <35fd1d0e.7537828@192.86.155.100>#1/1

A copy of this was sent to mark_tortolano_at_dial.pipex.com (Mark Tortolano) (if that email address didn't require changing) On Mon, 07 Sep 1998 12:09:49 GMT, you wrote:

>Hi,
>
>I have a view in my database that used to represent the fields in a
>single table of user preferences. Now, however, I want to split that
>user preferences table into two parts, so that the core preferences
>(eg user email, name) are in one table, and their other preferences
>(numerous, less important fields) are held in another table. There
>will be a one-to-one relationship between these tables.
>

why?

>I created the two tables using existing data from the one table, and
>made sure that the other preferences table references the main
>preferences table. I now realise, however, that Oracle will not let me
>make a view updateable when the view is created using a join, even if
>- it seems - the relationship between the tables is one-to-one.
>
>Is there an easy way around this, or do I have to go right through my
>application turning each UPDATE, INSERT, or DELETE into two separate
>SQL statements. This will be a real pain!
>

In Oracle7.2 and less -- you cannot do this. In 7.3, you will achieve limited success, in Oracle8 however, you can do the whole thing without problems (using instead of triggers).

here is an example:

SQL> create table tbl1 ( x int primary key, data1 varchar2(255) ); Table created.

SQL> create table tbl2 ( x int primary key references tbl1(x), data2 varchar2(255) );
Table created.

SQL> create or replace view tbl1_tbl2
  2 as
  3 select tbl1.x, tbl1.data1, tbl2.data2   4 from tbl1, tbl2
  5 where tbl1.x = tbl2.x
  6 /
View created.

SQL> insert into tbl1 values ( 1, 'data1' ); SQL> insert into tbl2 values ( 1, 'data1' );

SQL> select * from tbl1_tbl2;

         X DATA1 DATA2
---------- ---------- ----------

         1 data1 data1

SQL> update tbl1_tbl2 set data1 = 'new data1', data2 = 'new data2'; update tbl1_tbl2 set data1 = 'new data1', data2 = 'new data2'

                                          *

ERROR at line 1:
ORA-01776: cannot modify more than one base table through a join view

REM -- In 7.x, you'll get this error message and will not be able to 
REM -- get around it.  The workaround is to update with 2 statements 
REM -- as follows:

SQL> update tbl1_tbl2 set data1 = 'new data1'; 1 row updated.

SQL> update tbl1_tbl2 set data2 = 'new data2'; 1 row updated.

SQL> select * from tbl1_tbl2;

         X DATA1 DATA2
---------- ---------- ----------

         1 new data1 new data2

REM -- In Oracle8, using instead of triggers, we can use INSTEAD of 
REM -- triggers on views to perform the correct processing, this
REM -- update trigger shows how to make it so we can do the above
REM -- in one update statement:

SQL>
SQL> create or replace trigger tbl1_tbl2_instead_of_update   2 instead of update on tbl1_tbl2
  3 for each row
  4 begin

  5          update tbl1 set data1 = :new.data1;
  6          update tbl2 set data2 = :new.data2;

  7 end;
  8 /

Trigger created.

SQL>
SQL> update tbl1_tbl2 set data1 = 'newer data1', data2 = 'newer data2';

1 row updated.

SQL> select * from tbl1_tbl2;

         X DATA1 DATA2
---------- ----------- -----------

         1 newer data1 newer data2

>Thanks,
>
>Mark Tortolano
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.

Received on Mon Sep 07 1998 - 00:00:00 CDT