Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004
    Posts
    46

    Unanswered: How to query this scenario

    Hi all,

    I have 2 tables X and Y

    X: contains following data

    Table Name | Days
    A | 10
    B | 8
    C | 100
    D | 45

    Y: contains following structure

    Col1| col2 | col 3| col4 | col5 | ..|Table_Name


    How do I delete records from table Y basing on the number in Days column in Table X.

    Hope I am clear.


    Thanks
    Mark.

  2. #2
    Join Date
    Oct 2004
    Location
    Norway
    Posts
    53
    delete from table y t1
    where
    t1.table_name in
    ( select
    t2.table_name
    from
    x t2
    where
    t2.days = :value );

    ---

    I guess this should work, rewrite it to a select:

    select t1.table_name from table y t1
    where
    t1.table_name in
    ( select
    t2.table_name
    from
    x t2
    where
    t2.days = :value );

    In order to verify that the correct rows will be deleted.

    /pF

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    You should store the date of the record in a column of Y to be able to let this work.

    This is what this SQL script does: delete all records from table Y if the date of the record (Y.DA_CREATE) plus the number of X.Days days it should be kept is smaller or equal to the current date.

    DELETE
    FROM Y
    WHERE EXISTS
    (SELECT *
    FROM X
    WHERE X.Table_Name = Y.Table_Name and
    Y.DA_CREATE + X.DAYS DAY <= CURRENT DATE
    )
    ;

    Y.DA_CREATE + X.DAYS DAY <= CURRENT DATE action
    2004/12/02 0 2004/12/03 delete
    2004/12/02 1 2004/12/03 delete
    2004/12/02 2 2004/12/03 don't delete

    Assuming that X.DAYS = 1 means: keep it only the day the record was made (i.e. 2004/12/02).

    Wim

Posting Permissions

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