How to Write a SQL for Deleting Tables 

Question:
I have 8 tables for example A,B,.....H.

Now from these tables I need to delete record based on a criteria as date (something like -delete all records from these tables created before 30th march). Now these column has constraints and has to be deleted in the order A,B.....H.

These are the common column in all the tables......Parent ID and Created_date

Can I get some advice on how it can be acheived? I can delete it individually by running a delete statment, as you all know that is fairly simple. But I have around 6000 rows so its difficult.

If you have control over your constraints, maybe try setting them up as DELETE CASCADE :

SQL> create table parent (pk number primary key);

Table created.

SQL> create table child (pk number primary key,
  2                      fk number references parent(pk) 
  3                         on delete cascade);

Table created.

SQL> create table child_of_child (pk number primary key,
  2                               fk number references child(pk)
  3                                  on delete cascade);

Table created.

SQL> insert into parent values (1);
SQL> insert into child values (10, 1);
SQL> insert into child_of_child values (100, 10);

SQL> insert into parent values (2);
SQL> insert into child values (20, 2);
SQL> insert into child_of_child values (200, 20);

SQL> insert into parent values (3);
SQL> insert into child values (30, 3);
SQL> insert into child_of_child values (300, 30);

9 rows inserted

SQL> select * from parent;

        PK
----------
         1
         2
         3

SQL> select * from child;

        PK         FK
---------- ----------
        10          1
        20          2
        30          3

SQL> select * from child_of_child;

        PK         FK
---------- ----------
       100         10
       200         20
       300         30

Then, when you delete from the parent, the children go with :

SQL> delete from parent where pk in (1,2);

2 rows deleted.

SQL> select * from parent;

        PK
----------
         3

SQL> select * from child;

        PK         FK
---------- ----------
        30          3

SQL> select * from child_of_child;

        PK         FK
---------- ----------
       300         30

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Home
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © www.erpgreat.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site www.erpgreat.com is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of
www.erpgreat.com or the content authors.