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!

Oracle is doing precisely what you asked for. You asked Oracle to delete all rows present in an inner join from both tables.

Try instead:

DELETE FROM a
WHERE a.field IN (

   SELECT b.field
   FROM b);

Alternatively you might look at the EXISTS, INTERSECT, and MINUS operators.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)