Note when I follow the oradoc example (bolded large font below):
DELETE Statements
You can delete from a join view provided there is one and only one key-preserved table in the join. The key-preserved table can be repeated in the FROM clause.
The following DELETE statement works on the emp_dept view:
DELETE FROM emp_dept WHERE ename = 'SMITH';
This DELETE statement on the emp_dept view is legal because it can be translated to aDELETE operation on the base emp table, and because the emp table is the only key-preserved table in the join.
In the following view, a DELETE operation is permitted, because although there are two key-preserved tables, they are the same table. That is, the key-preserved table is repeated. In this case, the delete statement operates on the first table in the FROM list (e1, in this example):
CREATE VIEW emp_emp
AS SELECT e1.ename, e2.empno, e2.deptno
FROM emp e1, emp e2
WHERE e1.empno = e2.empno;
I tried the above and it worked great. here is my version of how I tried it
CREATE VIEW emp_emp
AS SELECT e1.last_name, e2.employee_id, e2.department_id
FROM employees2 e1, employees e2
WHERE e1.employee_id = e2.employee_id; /* (employees table in HR schema)*/
delete from emp_emp where employee_id between 100 and 190;
then I tried my own and not so much luck. Note in the below example I am using the employees table from HR schema
In any case here it is:
CREATE VIEW Emp_test AS
SELECT e1.last_name last_name , e1.employee_id, e2.department_id
FROM Employees2 e1, Employees2 e2
WHERE e1.employee_id = e2.employee_id;
delete from emp_test where employee_id = 100;
Note there is one key preserved table here, my underlying employees2 table.
Error: ORA-01752: cannot delete from view without exactly one key-preserved table
01752. 00000 - "cannot delete from view without exactly one key-preserved table"
*Cause: The deleted table had
- no key-preserved tables,
- more than one key-preserved table, or
- the key-preserved table was an unmerged view.
*Action: Redefine the view or delete it from the underlying base tables.
In this case it is just repeated and as the doc says:
DELETE Statements
You can delete from a join view provided there is one and only one key-preserved table in the join. The key-preserved table can be repeated in the FROM clause.
http://docs.oracle.com/database/121/ADMIN/views.htm#ADMIN11782
What am I missing in my script...? I tried a few variations all no luck, I can't figure out what's wrong.
In the doc if you ctrl+f the word 'repeat' it will make finding repeated key preserved topics much easier (that's the only time the word repeat is used)