Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> [Q] Not using a 'not in' in a 'delete' SQL statement: was not using a 'not in' SQL

[Q] Not using a 'not in' in a 'delete' SQL statement: was not using a 'not in' SQL

From: Chris Muir <cmuir_at_mitswa.com.au.no.spam>
Date: Thu, 20 Aug 1998 02:22:32 GMT
Message-ID: <35db863d.747726023@mitswa>


Howdy.

There has been some discussions on how to avoid using a 'not in' operator in a SQL statement recently. Suggestions have been to utilise 'exists', 'minus' and outer join queries.

I'm currently removing data from our database. The following structures exist:

table orange
(

  orange_pk number
  pips number
);

table apple
(

  apples_pk number,
  orange_ref_1 number references orange(orange_pk),
  orange_ref_2 number references orange(orange_pk),
  orange_ref_3 number references orange(orange_pk)
);

I wish to delete all oranges which aren't referenced by apples.

I'm stuck because it appears the 'not exists' operator is invalid in a delete statement. What is the fastest and most efficient way to delete the records?

Thanks in advance. Your help is appreciated :)

Chris Muir
chris.muir_at_mitswa.com.au.no.spam

If you would like to email me just remove the '.no.spam' from the above email address.    Received on Wed Aug 19 1998 - 21:22:32 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US