Removing rows from parent and child tables

Question

Assume two tables in Oracle 10G

TableA (Parent) --> TableB (Child) 

Each row in TableA has several child rows associated with it in TableB.

I want to delete specific rows in TableA, which means that I need to delete the related rows in tableB first.

This removes the child entries.

 delete from tableB where last_update_Dtm = sysdate-30; 

To remove parent rows for 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 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 to know which rows to delete without entries in the child table.

I can save the keys in the child table until deleted, but this seems like an expensive approach. What is the correct way to delete rows in both tables?

Edit

To better explain what I'm trying to achieve, the following query would do what I am trying to do if there were no restrictions between the two tables.

 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 

Answer 1

Two possible approaches.

• If you have a foreign key, declare it as on-delete-cascade and delete parent lines older than 30 days. All child lines will be deleted automatically.

• Based on your description, it looks like you know the parent lines you want to delete, and you need to delete the corresponding child lines. 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 parent table entries

   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 anyone can post a clean SQL solution on it (in this case, of course, this will be the way).

 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; / 

Answer 2

If children have FKs linking them to their parent, you can use DELETE CASCADE for the parent.

eg.

 CREATE TABLE supplier ( supplier_id numeric(10) not null, supplier_name varchar2(50) not null, contact_name varchar2(50), CONSTRAINT supplier_pk PRIMARY KEY (supplier_id) ); CREATE TABLE products ( product_id numeric(10) not null, supplier_id numeric(10) not null, CONSTRAINT fk_supplier FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id) ON DELETE CASCADE ); 

Remove the vendor and it will process all the products for that vendor.

Answer 3

Here is a complete example of how this can be done. However, you need flashback request privileges in the child table.

Here is the setting.

 create table parent_tab (parent_id number primary key, val varchar2(20)); create table child_tab (child_id number primary key, parent_id number, child_val number, constraint child_par_fk foreign key (parent_id) references parent_tab); insert into parent_tab values (1,'Red'); insert into parent_tab values (2,'Green'); insert into parent_tab values (3,'Blue'); insert into parent_tab values (4,'Black'); insert into parent_tab values (5,'White'); insert into child_tab values (10,1,100); insert into child_tab values (20,3,100); insert into child_tab values (30,3,100); insert into child_tab values (40,4,100); insert into child_tab values (50,5,200); commit; select * from parent_tab where parent_id not in (select parent_id from child_tab); 

Now remove the subset of children (with parents 1,3 and 4, but not 5).

 delete from child_tab where child_val = 100; 

Then, get parent_ids from the current state of COMMITTED child_tab (i.e. before they were before the deletion), and delete those that your session did NOT delete. This gives you the subset that has been deleted. Then you can remove them from parent_tab

 delete from parent_tab where parent_id in (select parent_id from child_tab as of scn dbms_flashback.get_system_change_number minus select parent_id from child_tab); 

Green still exists (since it still does not have an entry in the child table), and Red still exists (since it still has an entry in the child table)

 select * from parent_tab where parent_id not in (select parent_id from child_tab); select * from parent_tab; 

This is an exotic / unusual operation, so if I did this, I would probably be a little careful and lock the child and parent tables in exclusive mode at the start of the transaction. Also, if the child table was large, it would not be particularly efficient, so I would choose a PL / SQL solution such as Rajesh's.