You will never delete from more than one base table from a join view. DML on join view can only modify one base table. According to ORACLE DOC, you can delete from a join view if it has one and only one key-preserved table. Not sure about the previous versions of ORACLE, but in 9i this seems not to be true anymore. You can have more than 1 key-preserved table in a join view and ORACLE would still allow you to run delete on it. Which table is ORACLE deleting from? It is always the first table in the "FROM" list, or if you use the ANSI join syntax, the table that is being joined. In the following example, both a1 and a2 are key-preserved. But ORACLE has no problem deleting from it. Interestingly, I tested an example from ORACLE DOC under the "Deleting from a Join View" section and the delete succeeded also. When one of tables is not key-preserved, row will be deleted from the non-key-preserving table -> the bottom of the page. SQL> select constraint_name,constraint_type from user_constraints where table_name='A1'; CONSTRAINT_NAME C ------------------------------ - A1_PK P SQL> select constraint_name,constraint_type from user_constraints where table_name='A2'; CONSTRAINT_NAME C ------------------------------ - A2_PK P SQL> select * from a1; C1 C2 C3 -- ---------- ------ G 10 G_tag O 20 O_tag Q 30 Q_tag b 50 b_tag c 60 c_tag d 70 d_tag e 80 e_tag f 90 f_tag g 100 g_tag h 110 h_tag t 2 t_tag C1 C2 C3 -- ---------- ------ r 4 r_tag v 6 v_tag 13 rows selected. SQL> select * from a2; C1 C2 COL3 -- ---------- ------ G 110 110row O 120 120row Q 130 130row a 140 140row b 150 150row c 160 160row d 170 170row e 180 180row f 90 90row g 200 200row h 210 210row 11 rows selected. SQL> SQL> create or replace view view1$ as 2 select a1.c1,a2.c2,a1.c3 from a1,a2 3 where a1.c1=a2.c1; View created. SQL> SQL> -- a1 updated SQL> update view1$ set c3='newtag' where c1='b'; 1 row updated. SQL> rollback; Rollback complete. SQL> SQL> -- a2 updated SQL> update view1$ set c2=77 where c1='b'; 1 row updated. SQL> select * from a1 where c2=77; no rows selected SQL> select * from a2 where c2=77; C1 C2 COL3 -- ---------- ------ b 77 150row SQL> rollback; Rollback complete. SQL> SQL> -- a2 updated SQL> update view1$ set c2=77 where c2=90; 1 row updated. SQL> select * from a1 where c2=77; no rows selected SQL> select * from a2 where c2=77; C1 C2 COL3 -- ---------- ------ f 77 90row SQL> rollback; Rollback complete. SQL> SQL> -- a1 deleted SQL> select count(1) from a1; COUNT(1) ---------- 13 SQL> select count(1) from a2; COUNT(1) ---------- 11 SQL> delete view1$ where c1='b'; 1 row deleted. SQL> select count(1) from a1; COUNT(1) ---------- 12 SQL> select count(1) from a2; COUNT(1) ---------- 11 SQL> rollback; Rollback complete. SQL> SQL> -- a1 deleted SQL> delete view1$ where c2=90; 1 row deleted. SQL> select count(1) from a1; COUNT(1) ---------- 12 SQL> select count(1) from a2; COUNT(1) ---------- 11 SQL> rollback; Rollback complete. SQL> SQL> create or replace view view1$ as 2 select a2.c1,a2.c2,a1.c3 from a2,a1 3 where a1.c1=a2.c1; View created. SQL> SQL> -- a2 deleted SQL> delete view1$ where c2=90; 1 row deleted. SQL> select count(1) from a1; COUNT(1) ---------- 13 SQL> select count(1) from a2; COUNT(1) ---------- 10 SQL> rollback; Rollback complete. SQL> SQL> -- Won't work, column c2 ambiguous SQL> update (select * from a1,a2 where a1.c1=a2.c1) set c2=0 where c3='b_tag'; update (select * from a1,a2 where a1.c1=a2.c1) set c2=0 where c3='b_tag' * ERROR at line 1: ORA-00918: column ambiguously defined SQL> SQL> -- a1 deleted SQL> delete from (select * from a1,a2 where a1.c1=a2.c1) where c3='b_tag'; 1 row deleted. SQL> select count(1) from a1; COUNT(1) ---------- 12 SQL> select count(1) from a2; COUNT(1) ---------- 11 SQL> rollback; Rollback complete. SQL> SQL> -- a1 deleted SQL> delete from (select * from a1 inner join a2 on a1.c1=a2.c1) where c3='b_tag'; 1 row deleted. SQL> select count(1) from a1; COUNT(1) ---------- 12 SQL> select count(1) from a2; COUNT(1) ---------- 11 SQL> rollback; Rollback complete. SQL> SQL> -- a2 deleted SQL> delete from (select * from a2,a1 where a1.c1=a2.c1) where c3='b_tag'; 1 row deleted. SQL> select count(1) from a1; COUNT(1) ---------- 13 SQL> select count(1) from a2; COUNT(1) ---------- 10 SQL> rollback; Rollback complete. SQL> SQL> -- a2 deleted SQL> delete from (select * from a2 inner join a1 on a1.c1=a2.c1) where c3='b_tag'; 1 row deleted. SQL> select count(1) from a1; COUNT(1) ---------- 13 SQL> select count(1) from a2; COUNT(1) ---------- 10 SQL> rollback; Rollback complete. SQL> SQL> -- a1 deleted SQL> delete from (select * from a1 inner join a2 on a1.c1=a2.c1) where col3='90row'; 1 row deleted. SQL> select count(1) from a1; COUNT(1) ---------- 12 SQL> select count(1) from a2; COUNT(1) ---------- 11 SQL> rollback; Rollback complete. SQL> SQL> CREATE TABLE Dept_tab ( 2 Deptno NUMBER(4) PRIMARY KEY, 3 Dname VARCHAR2(14), 4 Loc VARCHAR2(13)); Table created. SQL> SQL> CREATE TABLE Emp_tab ( 2 Empno NUMBER(4) PRIMARY KEY, 3 Ename VARCHAR2(10), 4 Job varchar2(9), 5 Mgr NUMBER(4), 6 Hiredate DATE, 7 Sal NUMBER(7,2), 8 Comm NUMBER(7,2), 9 Deptno NUMBER(2), 10 FOREIGN KEY (Deptno) REFERENCES Dept_tab(Deptno)); Table created. SQL> insert into Dept_tab select * from scott.dept; 4 rows created. SQL> commit; Commit complete. SQL> insert into Emp_tab select * from scott.emp; 16 rows created. SQL> commit; Commit complete. SQL> drop view emp_emp; View dropped. SQL> CREATE VIEW emp_emp AS 2 SELECT e1.Ename, e2.Empno, e1.Deptno 3 FROM Emp_tab e1, Emp_tab e2 4 WHERE e1.Empno = e2.Empno; View created. SQL> delete from emp_emp; 16 rows deleted. SQL> rollback; Rollback complete. SQL> SQL> spool off SQL> alter table a2 drop constraint a2_pk; Table altered. SQL> delete from (select * from a2,a1 where a1.c1=a2.c1) where c3='b_tag'; 1 row deleted. SQL> select count(1) from a1; COUNT(1) ---------- 13 SQL> select count(1) from a2; COUNT(1) ---------- 10 SQL> rollback; Rollback complete. SQL> delete from (select * from a1,a2 where a1.c1=a2.c1) where c3='b_tag'; 1 row deleted. SQL> select count(1) from a1; COUNT(1) ---------- 13 SQL> select count(1) from a2; COUNT(1) ---------- 10 SQL> rollback; Rollback complete. SQL> delete from (select * from a1 inner join a2 on a1.c1=a2.c1) where col3='90row'; 1 row deleted. SQL> select count(1) from a1; COUNT(1) ---------- 13 SQL> select count(1) from a2; COUNT(1) ---------- 10 SQL> rollback; Rollback complete. SQL>