How ro store rowids in order to delete them after corresponding child records are deleted

Question

I would like to delete certain rows that i collect with the following select:

DELETE from REMINDER 
where exists 
(
SELECT r.rowid
FROM reminder r
inner join reminder_users u on u.reminder_id = r.id
inner join device d on d.id = (regexp_replace(r.origin_values, '[^0-9]', '')) and d.NEXT_TEST_DATE_INTERNAL <> u.deadline
inner join device_test t on t.id = d.NEXT_TEST_INT_ID
where u.receipt = 0 and t.TEST_INTERNAL_EXTERNAL = 0 and r.NAME like '%Interne%' and r.NAME not like '%Externe%' and u.DEADLINE > sysdate
);

The problem is, that there is a table 'reminder_users' that contains child records for the parent-table 'Reminder'. So I have to delete the child records first with the following statement:

DELETE from REMINDER_USERS
where exists
(
SELECT u.ROWID
from reminder_users u
inner join reminder r on r.id = u.reminder_id
inner join device d on d.id = (regexp_replace(r.origin_values, '[^0-9]', '')) and d.NEXT_TEST_DATE_INTERNAL <> u.deadline
inner join device_test t on t.id = d.NEXT_TEST_INT_ID
where u.receipt = 0 and t.TEST_INTERNAL_EXTERNAL = 0 and r.NAME like '%Interne%' and r.NAME not like '%Externe%' and u.DEADLINE > sysdate
);

When I perform the second operation, the first statement returns obviously zero rows, because I deleted the child records. This is why I am looking for a way to store the rowids from the first operation, delete the child records afterwards and finally delete the previously stored data from the table 'REMINDER'.

Do I have to use a CURSOR here? (Note: If the querys return rows, there will always be at least 2 rows affected). I tried to declare a variable that stores the rows, but then I get an 'ORA-01422: exact fetch returns more than requested number of rows' error...

Thanks in advance!

Answer 1

You should definitely have a foreign key constraint between reminder and reminder_users. You shouldn't be able to delete out of reminder if there is a child record in reminder_users. That's just basic database referential integrity.

If that foreign key is defined as on delete cascade, you're done. Deleting out of reminder will delete the dependent records in reminder_users.

Assuming you can't do that, one thing you can do is grab the ids to delete first, then delete them. You need to create a schema-level type:

create type id_tt as table of number;

declare
    l_reminder_ids id_tt;
begin
    select r.id 
      bulk collect into l_reminder_ids
      from reminder r
     inner join reminder_users u on u.reminder_id = r.id
     inner join device d 
             on d.id = (regexp_replace(r.origin_values, '[^0-9]', '')) 
            and d.NEXT_TEST_DATE_INTERNAL <> u.deadline
     inner join device_test t on t.id = d.NEXT_TEST_INT_ID
     where u.receipt = 0 
       and t.TEST_INTERNAL_EXTERNAL = 0 
       and r.NAME like '%Interne%' 
       and r.NAME not like '%Externe%' 
       and u.DEADLINE > sysdate
       for update;

    delete from reminder_users where reminder_id member of l_reminder_ids;

    delete from reminders where id member of l_reminder_ids;
end;

The for update clause is there to prevent some other session from coming in and messing with those rows while you're deleting from reminder_users. (Hmm, now that I think about it, that may not be necessary here.) This assumes the number of rows you are trying to delete is "reasonable." If you're trying to delete 20 million rows, this may not have very good performance.