Re: Update from 3way join

have you read this from oracle metalink?

The concept of a key-preserved table is fundamental to understanding the restrictions on modifying join views. A table is key preserved if every key of the table can also be a key of the result of the join. So, a key-preserved table has its keys preserved through a join. The key-preserving property of a table does not depend on the actual data in the table. It is, rather, a property of its schema. For example, if in the EMP table there was at most one employee in each department, then DEPTNO would be unique in the result of a join of EMP and DEPT, but DEPT would still not be a key-preserved table.

The following example shows an UPDATE statement that successfully modifies the EMP_DEPT view:

UPDATE emp_dept
SET sal = sal * 1.10
WHERE deptno = 10;

The following UPDATE statement would be disallowed on the EMP_DEPT view:

UPDATE emp_dept
SET loc = 'BOSTON'
WHERE ename = 'SMITH';

This statement fails with an ORA-01779 error (''cannot modify a column which maps to a non key-preserved table''), because it attempts to modify the base DEPT table, and the DEPT table is not key preserved in the EMP_DEPT view.

The following insert succedes if all other clumns in emp table are nullable.

insert into emp_dept(ename)
values('xxxx');

The following statement will fail with ora-01779 owing to the above reasons. insert into emp_dept(dname)
values('xxxx');

Hope this helps.

Best regards,
Giribabu Bhamidipati,
OSS. Received on Tue May 07 2002 - 19:56:18 CDT