RE: Cleanup of child records...

[Michael Rosenblum]

Title: Message

I would propose one more solution:

 

delete from <child_table>
where rowid in
                            (
                            select <child_table>.rowid
                            from <child_table>
                                minus
                            select <child_table>.rowid
                            from <child_table>,
                                     <parent_table>
                        where <child_table>.<fk_field> = <parent_table>.<fk_field>
                        )

In some cases it may be faster. But it's strongly recommended to have <fk_field> indexed on both tables.

Michael Rosenblum,

DBA. Dulcian, Inc.

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Magaliff, Bill
Sent: Thursday, June 13, 2002 1:30 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Cleanup of child records...

delete from <child_table> where <fk_field> not in

(select <pk_field> from <parent_table>);

 

or

 

delete from <child_table> where not exists

(select <pk_field> from <parent_table>

where <parent_table>.<pk_field> = <child_table>.<fk_field>);

 

not exists usually out performs not in, but both will work

 

bill

 

-----Original Message-----
From: Richard Huntley [mailto:[EMAIL PROTECTED]]
Sent: Thursday, June 13, 2002 12:59 PM
To: Multiple recipients of list ORACLE-L
Subject: Cleanup of child records...

TIA List,

What is the most efficient way to remove child records from a table
that have no parent records in it's parent table. I want to build a FK,
to keep this from happening, but I need to do some cleanup first.