Re: Possible Bug with: DELETE FROM (SELECT * FROM TABLE)

callcutj_at_mail.com wrote:
> I am attempting to use the following DELETE FROM format with the intent
> of deleting the records in TABLE_A that appear within TABLE_B:
>
> DELETE FROM (SELECT a.* FROM TABLE_A a JOIN TABLE_B b ON a.FIELD =
> b.FIELD)
>
> where a.FIELD is defined as a PK; there is no PK, index, etc. defined
> on b.FIELD
>
>
> When I execute this statement, I am finding that the records in TABLE_B
> are being deleted instead of TABLE_A! This is re-enforced when I look
> at the explain plan:
>
> Operation Object
> ------------------------------ ------------------------------
> DELETE STATEMENT ()
> DELETE () TABLE_B <--- I'd expect TABLE_A
> NESTED LOOPS ()
> TABLE ACCESS (FULL) TABLE_B
> INDEX (UNIQUE SCAN) TABLE_A
>
> I am using:
>
> SQL*Plus: Release 9.2.0.1.0 - Production on Tue Oct 4 13:10:44 2005
> Oracle9i Release 9.2.0.6.0 - Production
> JServer Release 9.2.0.6.0 - Production
>
>
> Note: I cannot use the "IN" clause (which does properly delete from
> TABLE_A), e.g:
>
> SELECT a.* FROM TABLE_A WHERE a.FIELD IN (SELECT b.FIELD FROM TABLE_B
> b)
>
> because I may need to join on several fields, eg.:
>
> DELETE FROM (SELECT a.* FROM TABLE_A a JOIN TABLE_B b ON a.FIELD1 =
> b.FIELD1 AND a.FIELD2 = b.FIELD2)
>
> Any help or insight into this would be greatly appreciated. Is it
> possible I have found a bug???
>
> Thanks in advance.

Same behaviour here (9.2.0.1 on linux):-

SQL> create table table_a (mypk integer, data varchar2(10));

Table created.

SQL> alter table table_a add constraint table_a_pk   2 primary key (mypk);

Table altered.

SQL> insert into table_a
  2 select level, 'data'
  3 from dual
  4 connect by level < 10;

9 rows created.

SQL> commit;

Commit complete.

SQL> create table table_b (notpk integer, data varchar2(10));

Table created.

SQL> insert into table_b
  2 select level, 'data'
  3 from dual
  4 connect by level < 10;

9 rows created.

SQL> commit;

Commit complete.

SQL> set autot on

SQL> L
  1 delete from
  2 ( select a.*
  3 from
  4 table_a a, table_b b
  5 where a.mypk = b.notpk
  6* )
SQL> / 9 rows deleted.

Execution Plan

   0 DELETE STATEMENT Optimizer=CHOOSE    1 0 DELETE OF 'TABLE_B'

   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'TABLE_B'
   4    2       INDEX (UNIQUE SCAN) OF 'TABLE_A_PK' (UNIQUE)

An interesting point is this, if you add a primary key to table_B, it deletes the rows from table_a instead:-

SQL> alter table table_b add constraint table_b_pk   2 primary key (notpk);

SQL> delete from
  2 ( select a.*
  3 from table_a a, table_b b
  4 where a.mypk = b.notpk);

9 rows deleted.

SQL> select count(*) from table_a;

  COUNT(*)

         0

SQL> select count(*) from table_B;

  COUNT(*)

         9

Seems like strange behaviour to me, but hopefully someone else can comment on it. I know that if you try to update the results of a join, both tables must be joined on a PK or it will refuse to do it. Received on Tue Oct 04 2005 - 16:04:39 CDT