Re: Delete records

From: Dave McRae <dmcrae_at_pcug.org.au>
Date: 1998/02/10
Message-ID: <6bp95b$n6g$2@bomb.dynamite.com.au>#1/1

G'Day Casper,

                            I once had the same problem: how to delete rogue

records from a table that didn't have corresponding records in one or the other of the two sub-type tables. The unfortunate thing about mine was that these tables were huge and doing a 'NOT IN' clause caused my SQL to run forever. If your tables are large and/or you are in need of speed try:

delete from table1 a
where a.id in

    (select x.id

     from table1 x, table2 y, table3 z
     where x.id=y.id(+)
      and x.id=z.id(+)
      and y.id is null
      and z.id is null)

You will be surprised (as I was) on how much faster this goes.

--
David McRae                    dmcrae_at_dynamite.com.au
Canberra                            02 6239 4247

Neil Korning Andersen wrote in message
<01bd359e$b863e020$e3371d93_at_dhcpi3381.csc.dk>...


>Hi Casper

> Try this

>

>delete from table1

>where id not in

>(select id from table2

>     union

>select id from table3)

>

>Hope this helps

>Neil

>Casper Thrane <ct_at_infoaccess.dk> skrev i artiklen

><34DF3C11.C9384677_at_infoaccess.dk>...

>> Hi!

>>

>> I have got this problem.

>> I have to delete all records from one table that doesn't exist in two

>> other tables.

>>

>> table 1

>> id

>> 1

>> 2

>> 3

>> 4

>> 5

>> 6

>>

>>

>> table 2 table 3

>> id id

>> 2 5

>> 3 6

>>

>> I want to delete all records from table 1 that has been left over, if

>> you combine table 2 and table 3. This means in this case, that I want to

>> delete id 1 and 4.

>>

>> Casper Thrane

>>

Received on Tue Feb 10 1998 - 00:00:00 CST