Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Posts
    4

    Unanswered: Deleting child records

    Hi All,

    I am using a database in which foreign key relationship is not enforced at the database level but at the application level.

    I am working on archiving an OLTP database, in which, after saving records older than, say, 1 year, into another database, these records should be deleted from the OLTP database.
    How can I delete child records based on a condition applied on one of the records of the parent table.

    Say parent_table is :
    Col1 varchar2
    Col2 varchar2
    Col3 DATE

    and child_table is:
    Col1 varchar2
    Col2 varchar2

    I need to delete from child_table when the corresponding parent table record's DATE Col3 is more than 12 months old.
    How can I do that ?
    Any help will be highly appreciated.

  2. #2
    Join Date
    May 2003
    Posts
    3

    Re: Deleting child records

    Originally posted by Muskaan
    Hi All,

    I am using a database in which foreign key relationship is not enforced at the database level but at the application level.

    I am working on archiving an OLTP database, in which, after saving records older than, say, 1 year, into another database, these records should be deleted from the OLTP database.
    How can I delete child records based on a condition applied on one of the records of the parent table.

    Say parent_table is :
    Col1 varchar2
    Col2 varchar2
    Col3 DATE

    and child_table is:
    Col1 varchar2
    Col2 varchar2

    I need to delete from child_table when the corresponding parent table record's DATE Col3 is more than 12 months old.
    How can I do that ?
    Any help will be highly appreciated.

    The delete statement can look something like this:
    delete from
    child_table
    where
    (Col1, Col2) in
    (select Col1, Col2
    from
    parent_table
    where
    Col3 < sysdate-365 -- Or whatever date expression fits
    );

    Then of course you can delete the parent rows.

    I think there is a way of deleting from both tables simultaneously in Oracle 9i using just one delete statement.

    Regards,

    Todd@loganweb.com

Posting Permissions

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