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

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Wed, 05 Oct 2005 08:44:06 +0200
Message-ID: <dhvsnd$9sh$00$1@news.t-online.com>

DA Morgan schrieb:
> 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.
>

This is not quite correct. Delete was asked for key preserved table from the subquery. Key preserved table is the table if every key of it can be also a key from the resulting join. It must be exactly one key preserved table in the join ( if none or more than one - an error occurs ). It is not a bug as well - it is many times documented on Metalink , though , maybe the SQL Reference is not quite clear about it.

> 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.

Best regards

Maxim Received on Wed Oct 05 2005 - 01:44:06 CDT