Deleting rows from multiple related tables

Question

There are three tables related one-to-one on the identifier. I need to delete all the records from the three tables that match the criteria A.ID = B.ID = C.ID

Now I do it in the following way:

DECLARE
    CURSOR CUR IS 
        SELECT C.ID FROM A 
        INNER JOIN B ON A."ID" = B."ID" 
        INNER JOIN C ON B."ID" = C."ID"
        WHERE A.STATUS = 'ERROR';
    IDX NUMBER;
BEGIN
    FOR REC IN CUR LOOP 
        IDX := REC.ID;
        DELETE FROM C WHERE C."ID" = IDX;
        DELETE FROM B WHERE B."ID" = IDX;
        DELETE FROM A WHERE BP."ID" = IDX;
    END LOOP;
    COMMIT;
END;

A lot of data and this way for very long runs. Is there any way to delete faster?

Why not put the ID values into a temp table and then delete from each of the tables on the join to the temp table? — , Oct 30 '12 at 05:32
If you have `Foreign Keys` on those tables you can add `ON DELETE CASCADE` — A.B.Cade, Oct 30 '12 at 07:17

score 3 · Accepted Answer · answered Oct 30 '12 at 09:10

You could create a PL/SQL type to store the IDs.

CREATE TYPE t_ids AS TABLE OF NUMBER;

Delete all records from table a that match the criterias, and return the IDs into a variable of that type. Then delete all records from b and c with these IDs.

DECLARE
  ids_to_delete   t_ids;
BEGIN
  DELETE FROM a
   WHERE a.status = 'ERROR'
     AND EXISTS ( SELECT 1 FROM b WHERE b.id = a.id )
     AND EXISTS ( SELECT 1 FROM c WHERE c.id = a.id )
  RETURNING a.id
  BULK COLLECT INTO ids_to_delete;

  DELETE FROM b
   WHERE id IN ( SELECT COLUMN_VALUE FROM TABLE( ids_to_delete ) );

  DELETE FROM c
   WHERE id IN ( SELECT COLUMN_VALUE FROM TABLE( ids_to_delete ) );
END;

This should perform a lot better, since it requires no loop and does everything in three SQL statements, instead of three statements per ID.

Deleting rows from multiple related tables

1 Answers1