| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: question on delete statement
"Fabio" <nonce_at_nulla.it> wrote in message news:<EdzIa.104026$Ny5.3014229_at_twister2.libero.it>...
> Hi
> I'm pretty new on Oracle so I'm sorry if this question was posted before.
>
> How is the behaviour of the DB engine on a complex delete query (I'm working
> on 9i)?
>
> If I had a query like
>
> delete (select * from A join B on A.f1=B.f1) C
> where C.f3 = '???' and C.f4 = 0
>
> which table (A or/and B) will be involved in the delete?
> How could I know if I'm deleting records from A table or B table or both ?
> Is there some syntax to specify the table I want delete records from?
>
> Thanks in advance
> Fabio
You will never delete from more than one base table from a join view. DML on join view can only modify one base table. Search for the "key-preserved table" on the ORACLE NGs or Metalink on this issue. 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 - table A in your case. 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.
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> 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> 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> 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> CREATE VIEW emp_emp AS
2 SELECT e1.Ename, e2.Empno, e1.Deptno 3 FROM Emp e1, Emp e2 4 WHERE e1.Empno = e2.Empno;
View created.
SQL> delete from emp_emp;
9 rows deleted.
SQL> rollback;
Rollback complete.
SQL>
 |
 |