Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Location
    Denmark
    Posts
    15

    Unanswered: deleting from one table based on rows in another

    I have 2 tables: OrderHeaders and OrderLines which has a one-to-many relationship.
    Depending on if the field CreateDate in OrderHeaders has expired som date, I want to delete the corresponding rows in OrderLines AND OrderHeaders... but how?

    The tables share the fields CompanyID, CustomerID, OrderNO.
    Last edited by caf78; 06-20-03 at 10:07.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    If OrderLines table was created with a foreign key constraint on OrderHeader with 'ON DELETE CASCADE' option, alll you have to do is:

    Delete From OrderHeader
    Where CreateDate <= ExpireDate;

    Else, you need first to remove the OrderLines:

    Delete From OrderLines L
    Where Exists (
    Select 1 From OrderHeader H
    Where H.CompanyID = L.CompanyID
    And H.CustomerID = L.CustomerID
    And H.OrderNO = L.OrderNO
    And L.CreateDate <= ExpireDate);

    And then execute the delete of OrderHeader.

  3. #3
    Join Date
    May 2003
    Posts
    87

    Re: deleting from one table based on rows in another

    You can have a daily process (procedure/function) scheduled to run at night to check for any such orders and then delete from OrderLines first and then from OrderHeaders.

    Or you could use DBMS_JOB to schedule the procedure/function to do this. Once you have written the correct procedure/function to do this, it is easy to schedule using DBMS_JOB.

    Say if you have a procedure 'test_job', you can schedule it as below :
    Code:
    declare
      l_job number;
    begin
      dbms_job.submit(l_job, 'test_job;',sysdate+1/200);
    end;
    /
    commit
    /
    Hope this helps !!

    Originally posted by caf78
    I have 2 tables: OrderHeaders and OrderLines which has a one-to-many relationship.
    Depending on if the field CreateDate in OrderHeaders has expired som date, I want to delete the corresponding rows in OrderLines AND OrderHeaders... but how?

    The tables share the fields CompanyID, CustomerID, OrderNO.

Posting Permissions

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