Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2009
    Posts
    2

    Wink Unanswered: How to write a SQL for deleting tables

    Hello Experts,

    I am not a very good in SQL but here is my requirement

    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.


    Feel free to write back if I need to clarify anything

    Thanks in advance
    db_1987

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    If you have control over your constraints, maybe try setting them up as DELETE CASCADE

    Code:
    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:
    Code:
    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
    --=Chuck

  3. #3
    Join Date
    Apr 2009
    Posts
    2
    Hello Chuck,

    Thanks for your reply.

    Let me try these and get back to you

    Regards,
    db_1987

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •