Results 1 to 4 of 4

Thread: DELETE Problem

  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Unanswered: DELETE Problem

    Hi Guys,

    I have this strange problem. Take the following SQL i wrote which
    erturns a list of dates lower than the one entered in a table :-

    SELECT * FROM HACPRP.EQ_POOL_ACT_VIEW
    WHERE ACTVTY_DT IN

    (SELECT ACTVTY_DT FROM HACPRP.EQ_POOL_ACT_VIEW

    WHERE SUBSTR(ACTVTY_DT,5,2) < '02'
    )

    UNION

    SELECT * FROM HACPRP.EQ_POOL_ACT_VIEW
    WHERE ACTVTY_DT IN

    (SELECT ACTVTY_DT FROM HACPRP.EQ_POOL_ACT_VIEW

    WHERE SUBSTR(ACTVTY_DT,1,2) < '03'
    AND
    SUBSTR(ACTVTY_DT,5,2) = '02'
    )

    UNION

    SELECT * FROM HACPRP.EQ_POOL_ACT_VIEW
    WHERE ACTVTY_DT IN

    (SELECT ACTVTY_DT FROM HACPRP.EQ_POOL_ACT_VIEW

    WHERE SUBSTR(ACTVTY_DT,3,2) < '27'
    AND
    SUBSTR(ACTVTY_DT,1,2) = '03'
    AND
    SUBSTR(ACTVTY_DT,5,2) = '02'
    );

    I had to do this in this format as whoever created the table made the ACTVTY_DT a CHAR(06) instead of a DATE !
    This works fine and gives me back my required results.

    The problem is however - How do I get SQL to delete these results now
    from the HACPRP.EQ_POOL_ACT_VIEW table ?

    No matter what i try it just falls over and will not delete the records.
    Is it possible to do ?

    I really need help on this one, so any comments would be really appreciated.

    Peter.

  2. #2
    Join Date
    Oct 2003
    Location
    Dallas
    Posts
    76

    ....

    just replace the select * with the word DELETE, most likely cannot do the UNION DELETE so it most likely will have to be three seperate statements without the union.

    let me know if that does not work

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Lightbulb

    Try this:

    Code:
    DELETE FROM HACPRP.EQ_POOL_ACT_VIEW
    WHERE ACTVTY_DT IN
    (SELECT ACTVTY_DT FROM HACPRP.EQ_POOL_ACT_VIEW
      WHERE SUBSTR(ACTVTY_DT,5,2)  < '02'
         OR (SUBSTR(ACTVTY_DT,1,2) < '03'
        AND  SUBSTR(ACTVTY_DT,5,2) = '02')
         OR (SUBSTR(ACTVTY_DT,3,2) < '27'
        AND  SUBSTR(ACTVTY_DT,1,2) = '03'
        AND  SUBSTR(ACTVTY_DT,5,2) = '02'));
    
    also:
    
    DELETE FROM HACPRP.EQ_POOL_ACT_VIEW
    WHERE TO_DATE(ACTVTY_DT,'MMDDYY') < '27-MAR-2003';
    Last edited by LKBrwn_DBA; 10-27-03 at 18:05.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    or just type delete from ( <your query)

    hope this helps
    Edwin van Hattem
    OCP DBA / System analyst

Posting Permissions

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