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

From: <Mujeeb_ur_Rehman_at_hotmail.com>
Date: Thu, 20 Aug 1998 06:26:57 GMT
Message-ID: <6rgfjg$gec$1@nnrp1.dejanews.com>

Hi :)

Run this statement and it should remove all your oranges :).

Hope this helps.

---

delete from oranges x
where not exists
 ( select ':)' from apples y

   where  y.orange_ref_1 = x.orange_pk or
          y.orange_ref_2 = x.orange_pk or
          y.orange_ref_3 = x.orange_pk

  )
/

---

In article <35db863d.747726023_at_mitswa>,   cmuir_at_mitswa.com.au.no.spam (Chris Muir) wrote:
> 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.
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Thu Aug 20 1998 - 01:26:57 CDT