Delete Rows which are fetched by a select query joining several tables

I have written the following select:

SELECT r.ID, r.NAME, r.REMARK, u.user_id, u.deadline, u.creation_date, d.NEXT_TEST_DATE, t.TEST_INTERNAL_EXTERNAL FROM reminder r, reminder_users u, device d, device_test t where r.id = u.reminder_id and u.receipt = 0 and (regexp_replace(r.origin_values, '[^0-9]', '')) = d.id and d.NEXT_TEST_INT_ID = t.id  and t.TEST_INTERNAL_EXTERNAL = 0 and r.NAME like '%Interne%' and r.NAME not like '%Externe%' and u.deadline <> d.NEXT_TEST_DATE_INTERNAL and u.DEADLINE > sysdate GROUP BY r.ID, r.NAME, r.REMARK, u.user_id, u.deadline, u.audt_creation_date, d.NEXT_TEST_DATE_INTERNAL, t.TEST_INTERNAL_EXTERNAL ORDER BY u.deadline;

What I want to do: I want to delete the rows in reminder, which are fetched by the described select.

What I tried:

DELETE from REMINDER where id in (SELECT r.ID, r.NAME, r.REMARK, u.user_id, u.deadline, u.audt_creation_date, d.NEXT_TEST_DATE_INTERNAL, t.TEST_INTERNAL_EXTERNAL FROM reminder r, reminder_users u, device d, device_test t where r.id = u.reminder_id and u.receipt = 0 and (regexp_replace(r.origin_values, '[^0-9]', '')) = d.id and d.NEXT_TEST_INT_ID = t.id  and t.TEST_INTERNAL_EXTERNAL = 0 and r.NAME like '%Interne%' and r.NAME not like '%Externe%' and u.deadline <> d.NEXT_TEST_DATE_INTERNAL and u.DEADLINE > sysdate GROUP BY r.ID, r.NAME, r.REMARK, u.user_id, u.deadline, u.audt_creation_date, d.NEXT_TEST_DATE_INTERNAL, t.TEST_INTERNAL_EXTERNAL ORDER BY u.deadline);

Result: 00907. 00000 - "missing right parenthesis"

I wonder if i can basically delete these rows in this manner. I also tried ‘exists’ resulting in the same error.

Any suggestions? Thanks in advance!

Answer

Remove order by clause from your subquery and it should work:

DELETE from BSRMD_REMINDER where id in (SELECT r.ID FROM bsrmd_reminder r, bsrmd_reminder_users u, bsdev_device d, bsdev_device_test t where r.id = u.reminder_id and u.receipt = 0 and (regexp_replace(r.origin_values, '[^0-9]', '')) = d.id and d.NEXT_TEST_INT_ID = t.id  and t.TEST_INTERNAL_EXTERNAL = 0 and r.NAME like '%Interne%' and r.NAME not like '%Externe%' and u.deadline <> d.NEXT_TEST_DATE_INTERNAL and u.DEADLINE > sysdate GROUP BY r.ID, r.NAME, r.REMARK, u.user_id, u.deadline, u.audt_creation_date, d.NEXT_TEST_DATE_INTERNAL, t.TEST_INTERNAL_EXTERNAL );

Or better to use exists:

  DELETE from BSRMD_REMINDER 
where exists 
(
    SELECT 1 
    FROM bsrmd_reminder r, bsrmd_reminder_users u, bsdev_device d, bsdev_device_test t 
    where r.id = u.reminder_id and u.receipt = 0 and (regexp_replace(r.origin_values, '[^0-9]', '')) = d.id 
    and d.NEXT_TEST_INT_ID = t.id  and t.TEST_INTERNAL_EXTERNAL = 0 and r.NAME like '%Interne%' and r.NAME not like '%Externe%' 
    and u.deadline <> d.NEXT_TEST_DATE_INTERNAL and u.DEADLINE > sysdate 
    AND BSRMD_REMINDER.ID=r.ID
    GROUP BY r.ID, r.NAME, r.REMARK, u.user_id, u.deadline, u.audt_creation_date, 
    d.NEXT_TEST_DATE_INTERNAL, t.TEST_INTERNAL_EXTERNAL 
);

**Please check again whether all those joins are really necessary.