Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2009
    Posts
    38

    Unanswered: Delete From 2 Tables

    Hi I have two tables that are connected and i want to delete from both of them the connected rows that have no records for the last year. Can i do it at once? i did this but it only deletes the rows from table BUY and not the inactive ones for the last 365 days.

    buy table(code_client,date)

    client table(code_client,name)


    DELETE FROM BUY
    WHERE EXISTS
    ( select CLIENT.CODE_P
    from CLIENT
    where CLIENT.CODE_P=BUY.CODE_P
    );

    How can i use SYSDATE in it?


    Thank you a lot.
    Last edited by misty1976; 02-19-09 at 10:57.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by misty1976
    Hi i have two tables that are connected and i want to delete from both of them the connected rows. Can i do it at once? i did this but it only deletes the rows from table BUY
    No, a DELETE only deletes from a single table.
    You will need two DELETEs to delete from two tables.

    And how can i use SYSDATE? because i want to delete the records for the last year(365 days) and because i couldn't use it i put: ""BETWEEN TO_DATE('01-01-2008','DD,MM,YYYY') AND TO_DATE('31-12-2008','DD,MM,YYYY')""
    Code:
    WHERE BUY.DATE > sysdate - 365 * 24
    or
    Code:
    WHERE BUY.DATE > sysdate - INTERVAL '1' YEAR
    Don't forget that a DATE column also includes a time, you might actually want:
    Code:
    WHERE trunc(BUY.DATE) > trunc(sysdate - INTERVAL '1' YEAR)
    But be aware that this will disable the usage on a potential index on BUY.DATE.

    A final note: you should not use reserved keywords ("DATE") as the name of a column. That will solve you a lot of trouble.
    But what is even more important, a column named PURCHASE_DATE is a lot clearer than date. What date is meant? The date the order was placed, when it was delivered, when it was cancelled....
    Having good column names will help you understand the data model even years later.

  3. #3
    Join Date
    Feb 2009
    Posts
    62
    If you have a Primary / Foreign key relationship between the two tables, then you could set the foreign key to 'ON DELETE CASCADE'

    This would mean that whenever a parent record was deleted, all the records who's foreign key links pointed to it would also get get deleted.

  4. #4
    Join Date
    Feb 2009
    Posts
    38
    Quote Originally Posted by shammat
    No, a DELETE only deletes from a single table.
    You will need two DELETEs to delete from two tables.


    Code:
    WHERE BUY.DATE > sysdate - 365 * 24
    or
    Code:
    WHERE BUY.DATE > sysdate - INTERVAL '1' YEAR
    Don't forget that a DATE column also includes a time, you might actually want:
    Code:
    WHERE trunc(BUY.DATE) > trunc(sysdate - INTERVAL '1' YEAR)
    But be aware that this will disable the usage on a potential index on BUY.DATE.

    A final note: you should not use reserved keywords ("DATE") as the name of a column. That will solve you a lot of trouble.
    But what is even more important, a column named PURCHASE_DATE is a lot clearer than date. What date is meant? The date the order was placed, when it was delivered, when it was cancelled....
    Having good column names will help you understand the data model even years later.

    So i ' ll do it like that?

    DELETE FROM BUY
    WHERE EXISTS
    ( select CLIENT.CODE_P
    from CLIENT
    where CLIENT.CODE_P=BUY.CODE_P and WHERE BUY.DATE > sysdate - 365 * 24
    );

    and the same for CLIENTS?

    THANKS

  5. #5
    Join Date
    Feb 2009
    Posts
    38
    Quote Originally Posted by JRowbottom
    If you have a Primary / Foreign key relationship between the two tables, then you could set the foreign key to 'ON DELETE CASCADE'

    This would mean that whenever a parent record was deleted, all the records who's foreign key links pointed to it would also get get deleted.
    MY primary key is code_client for both according to the given data i have.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by misty1976
    So i ' ll do it like that?

    DELETE FROM BUY
    WHERE EXISTS
    ( select CLIENT.CODE_P
    from CLIENT
    where CLIENT.CODE_P=BUY.CODE_P and WHERE BUY.DATE > sysdate - 365 * 24
    );
    Why don't you just try it....

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    what about leap years. use the following

    Code:
    DELETE FROM BUY
    WHERE NOT EXISTS
    ( select CLIENT.CODE_P
    from CLIENT
    where CLIENT.CODE_P=BUY.CODE_P and WHERE BUY.DATE > add_months(sysdate,-12));
    The exists would delete everyting that had a buy_date in the last year, use NOT exists.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by beilstwh
    what about leap years. use the following
    Good point.
    It would be interesting if INTERVAL '1' YEAR handles that, but I doubt it

  9. #9
    Join Date
    Feb 2009
    Posts
    38
    Thank you all. I' ll try them today.

  10. #10
    Join Date
    Feb 2009
    Posts
    38
    Have one little problem. I deleted from one but now cant delete from the other because there is no connection any more

  11. #11
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    ROLLBACK and start over (this time with a more clever approach).

  12. #12
    Join Date
    Feb 2009
    Posts
    38
    Quote Originally Posted by Littlefoot
    ROLLBACK and start over (this time with a more clever approach).
    ok did it but still searching a way to do it at once.

  13. #13
    Join Date
    Feb 2009
    Posts
    38
    Something else
    I added a table D with cod_d as primary key. And i want to alter my other table E (THAT HAS ITS OWN PRIMARY KEY) so that it has cod_d in it.

    Is that the propre way?

    ALTER TABLE E
    ADD COD_D INT NOT NULL
    FOREIGN KEY (COD_D) REFERENCES D(COD_D)


    Thank you.

  14. #14
    Join Date
    Jan 2009
    Posts
    17
    alter table E add COD_D int constraint fk_k references D(COD_D);
    Last edited by sridhar.dbe; 02-20-09 at 09:15.

  15. #15
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Once you clear out your buy records, use the following to wipe out the client records with no buy records.

    DELETE FROM client
    WHERE NOT EXISTS
    (select null
    from buy
    where CLIENT.CODE_P=BUY.CODE_P);
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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