Deleting rows from parent and child tables

Question

Assume two tables in Oracle 10G  TableA  Parent  -- gt  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

User · Accepted Answer

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

User · Answer

Here s a complete example of how it can be done  However you need flashback query privileges on the child table   Here s the setup   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 delete a subset of the children  ones with parents 1 3 and 4 - but not 5    delete from child tab where child val   100    Then get the parent ids from the current COMMITTED state of the child tab  ie as they were prior to your deletes  and remove those that your session has NOT deleted  That gives you the subset that have been deleted  You can then delete those out of the 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  is still there  as it didn t have an entry in the child table anyway  and  Red  is still there  as 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    It is an exotic unusual operation  so if i was doing it I d probably be a bit cautious and lock both child and parent tables in exclusive mode at the start of the transaction  Also  if the child table was big it wouldn t be particularly performant so I d opt for a PL SQL solution like Rajesh s

User · Answer

If the children have FKs linking them to the parent  then you can use DELETE CASCADE on the parent   e g   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        Delete the supplier  and it will delate all products for that supplier

[sql] Deleting rows from parent and child tables

Examples related to sql

Examples related to oracle