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

<callcutj_at_mail.com> a écrit dans le message de news: 1128452715.817859.213890_at_o13g2000cwo.googlegroups.com...
|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.
|

Why can't you use:

delete table_a where (field1,field2) in (select field1, field2 from table_b)

or

delete table_a a where exists (select 1 from table_b b where b.field1=a.field1 and b.field2=a.field2)

Regards
Michel Cadot Received on Tue Oct 04 2005 - 15:33:31 CDT