The previous example had inserts and data modification on a view with one table defined in the FROM clause of the SQL query, but this is also possible with multiple tables defined. This is known as an updatable join view.

For reference purposes, here are the CREATE TABLE statements for the two tables used in the examples in this section:

SQL> create table dept ( dept_id number primary key , dept_name varchar2(15)); —

SQL> create table emp ( emp_id number primary key , emp_name varchar2(15), dep_id number, constraint emp_dept_fk foreign key (dept_id) references dept(dept_id));And let’s seed some data for the two tables:

SQL> insert into dept values (1, ‘HR’), (2, ‘IT’),(3, ‘SALES’);

SQL> insert into emp values (10, ‘John’, 2), (20, ‘George’, 1),

(30, ‘Fred’, 2), (40, ‘Craig’, 1), (50, ‘Linda’, 2), (60, ‘Carrie’, 3);

Here is an example of an updatable join view, based on the two prior base tables:

SQL> create or replace view emp_dept_v asselect a.emp_id, a.emp_name, b.dept_name, b.dept_id from emp a, dept bwhere a.dept_id = b.dept_id;

Underlying tables can be updated only if the following conditions are true:

•     The DML statement must modify only one underlying table.

•     The view must be created without the READ ONLY clause.

•     The column being updated belongs to the key-preserved table in the join view.

An underlying table in a view is key preserved if the table’s primary key can also be used to uniquely identify rows returned by the view. An example with data will help illustrate whether an underlying table is key preserved. In this scenario, the primary key of the EMP table is the EMP_ID column; the primary key of the DEPT table is the DEPT_ID column. Here is some sample data returned by querying the view:

As you can see from the output of the view, the EMP_ID column is always unique. Therefore, the EMP table is key preserved, and its columns can be updated. In contrast, the view’s output shows that it is possible for the DEPT_ID column to not be unique. Therefore, the DEPT table is not key preserved, and its columns can’t be updated.

When you update the view, modifications that result in columns that map to the underlying EMP table should be allowed because the EMP table is key preserved in this view.

SQL> update emp_dept_v set emp_name = ‘Jon’ where emp_id = 10;