SQL delete orphan

delete-row, oracle, sql

Solution

You might be able to use the extended `DELETE` statement in 10g that includes error logging.

First use `DBMS_ERRLOG` to create a logging table (which is just a copy of the original table with some additional prefixing columns: `ORA_ERR_MESG$, ..., ORA_ERR_TAG$`)

execute dbms_errlog.create_error_log('parent', 'parent_errlog');

Now, you can use the LOG ERRORS clause of the delete statement to capture all rows that have existing integrity constraints:

delete from parent
   log errors into parent_errlog ('holding-breath')
   reject limit unlimited;

In this case the "holding-breath" comment will go into the `ORA_ERR_TAG$` column.

You can read the full documentation here.

If the parent table is huge and you're only looking to delete a few stray rows, you'll end up with a `parent_errlog` table that is essentially a duplicate of your `parent` table. If this isn't ok, you'll have to do it the long way:

- Directly reference the child tables (following Tony's solution), or,

- Loop through the table in PL/SQL and catch any exceptions (following Confusion's and Bob's solutions).

Problem

Assuming that all foreign keys have the appropriate constraint, is there a simple SQL statement to delete rows not referenced anywhere in the DB? Something as simple as `delete from the_table` that simply skip any rows with child record? I'm trying to avoid manually looping through the table or adding something like `where the_SK not in (a,b,c,d)`.

Original source