Assume two tables in Oracle 10G
TableA (Parent) --> TableB (Child)
Every row in TableA has several child rows related to it in TableB.
I want to delete specific rows in TableA which means i have to delete the related rows in tableB first.
This deletes the child entries
delete from tableB where last_update_Dtm = sysdate-30;
To delete the parent rows for the rows just deleted in the child table I could do something like this
Delete from TableA where not exists (select 1 from tableB where tableA.key=tableB.key);
The above will will also delete rows in the child table where (last_update_Dtm = sysdate-30) is false. TableA does not have a last_update_dtm column so there is no way of knowing which rows to delete without the entries in the child table.
I could save the keys in the child table prior to deleting but this seems like an expensive approach. What is the correct way of deleting the rows in both tables?
Edit
To explain better what i am trying to achieve, the following query would have done what i am trying to do if there was no constraint between the two table.
Delete from tableA
Where exists (
Select 1 from tableB
where tableA.key=tableB.key
and tableB.last_update_dtm=sysdate-30)
Delete from tableB where last_update_dtm=systdate-30
Best Answer-推荐答案 strong>
Two possible approaches.
If you have a foreign key, declare it as on-delete-cascade and delete the parent rows older than 30 days. All the child rows will be deleted automatically.
Based on your description, it looks like you know the parent rows that you want to delete and need to delete the corresponding child rows. Have you tried SQL like this?
delete from child_table
where parent_id in (
select parent_id from parent_table
where updd_tms != (sysdate-30)
-- now delete the parent table records
delete from parent_table
where updd_tms != (sysdate-30);
---- Based on your requirement, it looks like you might have to use PL/SQL. I'll see if someone can post a pure SQL solution to this (in which case that would definitely be the way to go).
declare
v_sqlcode number;
PRAGMA EXCEPTION_INIT(foreign_key_violated, -02291);
begin
for v_rec in (select parent_id, child id from child_table
where updd_tms != (sysdate-30) ) loop
-- delete the children
delete from child_table where child_id = v_rec.child_id;
-- delete the parent. If we get foreign key violation,
-- stop this step and continue the loop
begin
delete from parent_table
where parent_id = v_rec.parent_id;
exception
when foreign_key_violated
then null;
end;
end loop;
end;
/
|