Re: Can you update more then one table through the view?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/02/18
Message-ID: <34ed2624.15523251@192.86.155.100>#1/1

A copy of this was sent to "Robert Augustyn" <augustyn_at_unn.unisys.com> (if that email address didn't require changing) On Wed, 18 Feb 1998 12:11:10 -0500, you wrote:

>Hi,
>I am trying to update a view and I am getting errors
>saying that I can not modify more then one base table through the view
>Any solutions
>thanks in advance
>robert
>

We need to see

the view
the relationship between the underlying tables
the database version.

to tell you more.

In oracle 7.3 you can in some cases update a multi-table join. For example, given the schema:

CREATE TABLE DEPT

       (DEPTNO NUMBER(2) primary key,
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );

CREATE TABLE EMP
       (EMPNO NUMBER(4) NOT NULL primary key,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7,2),
        COMM NUMBER(7,2),
        DEPTNO NUMBER(2) references dept(deptno));

create or replace view emp_dept_view
as
select empno, ename, emp.deptno, dname
from emp, dept
where emp.deptno = dept.deptno
/

We can see that:

SQL> l
1 select table_name, column_name, updatable 2 from user_updatable_columns
3* where table_name = 'EMP_DEPT_VIEW'

TABLE_NAME                     COLUMN_NAME                    UPD
------------------------------ ------------------------------ ---
EMP_DEPT_VIEW                  EMPNO                          YES
EMP_DEPT_VIEW                  ENAME                          YES
EMP_DEPT_VIEW                  DEPTNO                         YES
EMP_DEPT_VIEW                  DNAME                          NO

tells us that in the view emp_dept_view, 3 of the 4 columns are updatable. The columns that are updatable are the 'key preserved' columns. Since the relationship between DEPT to EMP is 1 to MANY, we cannot update the columns from the DEPT table in this view but we can update the columns in the EMP table -- the key preserved table.

In Oracle8, you can update virtually any view. In Oracle8 there are 'instead of' triggers you can place on views so you can specify the logic to perform 'instead of inserting/updating/deleteing' a view (in addition to the above ability as well)

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

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 Wed Feb 18 1998 - 00:00:00 CST