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

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