View problem - cannot update with a Join

View problem - cannot update with a Join

Post by Mark Tortola » Tue, 08 Sep 1998 04:00:00



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.

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!

Thanks,

Mark Tortolano

 
 
 

View problem - cannot update with a Join

Post by Winnie Li » Tue, 08 Sep 1998 04:00:00


When you create your view, have you made your view 'read only'? Or do you
have any privilege to change the base tables under the views? If you have
all the privilege to the two base tables and you did not made your view read
only, I never heard of such a situation happening.

Another way to get around is to write trigger on one of the table, so that
everytime when it got update/delete/insert, it will fire a trigger and
update/delete/insert on another table

Winnie


>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.

>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!

>Thanks,

>Mark Tortolano


 
 
 

View problem - cannot update with a Join

Post by Thomas Ky » Tue, 08 Sep 1998 04:00:00



(if that email address didn't require changing)


>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?

Quote:>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

Quote:>Thanks,

>Mark Tortolano

Thomas Kyte

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.

 
 
 

1. cannot modify more than one base table through a join view

Hi there!

I created a view that bases on two tables.
These tables connected at each other through a 1:1-reference
In the second table there is a primary key with a trigger that simulates a
AutoCount-Field.
In the first table there is a unique-indexed foreign key.

If I want to insert data (or change data in the second table) it gives the
message "cannot modify more than one base table through a join view".
Is there a fault in my query/view, or in which way you solve this problem?

Thanx for tips!

Stefan D.

2. any way to sort toolbar buttons

3. #ERROR 3027 Cannot Update Recordset Constructed by LEFT JOINED Access 97 Tables

4. Restricting/Predicting Rows to prevent log space overflowing

5. Cannot update the table Through View

6. SQLServer 2000 backup to an FTP server?

7. Why cannot update this simple view?

8. True DBGrid created with control arrays and populating

9. Cannot Update Views via QBF.?

10. Cannot update view...

11. instead of update (view with outer join

12. joined view updates

13. Updating/Adding data through a view/join