Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2013
    Posts
    7

    Unanswered: SQL to DELETE with LIMIT of n rows

    Hi,

    I need help writing a query to DELETE with a LIMIT (n rows).

    Here's my table:

    ( RPT_YR CHAR(4)
    , RPT_TCD CHAR(2)
    , RPT_DT DATE
    , RC_CD CHAR(4)
    , SOFP_ID CHAR(4)
    , ADD_ACCT_CNT INTEGER
    , MOD_ACCT_CNT INTEGER
    )


    Primary key of the table:

    RPT_YR CHAR(4) NOT NULL
    , RPT_TCD CHAR(2) NOT NULL
    , RPT_DT DATE NOT NULL
    , RC_CD CHAR(4) NOT NULL
    , SOFP_ID CHAR(4) NOT NULL


    The only value I get from the input is for RPT_YR.

    Ex: I have to delete any rows with RPT_YR < '2002'

    My Goal is to delete with a limit of 1000 rows at time.


    I've tried:
    DELETE FROM T1
    WHERE RPT_YR IN
    (SELECT RPT_YR
    FROM T1
    WHERE RPT_YR < '2002'
    FETCH FIRST 1000 ROWS ONLY
    )
    but of course, it deletes all rows where RPT_YR < '2002'. That's not what I want. I want to delete 1000 rows at a time.


    I've tried:
    DELETE FROM
    (SELECT RPT_YR
    FROM T1
    WHERE RPT_YR < '2002'
    FETCH FIRST 1000 ROWS ONLY
    )
    ;
    but does not work, I get SQLCODE = -104. DB2 does not like the ( after DELETE FROM


    I've tried:
    DELETE FROM T1
    WHERE RPT_YR < '2002'
    FETCH FIRST 1000 ROWS ONLY
    ;
    But does not work, I get SQLCODE = -199. DB2 does not like the FETCH FIRST on the DELETE


    Can I make this work?

    Thanks guys.
    C

  2. #2
    Join Date
    Sep 2013
    Posts
    7
    I just tried:

    DELETE FROM T1
    WHERE T1.RPT_YR < '2002'
    AND EXISTS
    (SELECT *
    FROM T2
    WHERE T2.RPT_YR = T1.RPT_YR
    AND T2.RPT_TCD = T1.RPT_TCD
    AND T2.RPT_DT = T1.RPT_DT
    AND T2.RC_CD = T1.RC_CD
    AND T2.SOFP_ID = T1.SOFP_ID
    FETCH FIRST 1000 ROWS ONLY
    )
    ;

    I thought it would delete 1000 rows only, but it deletes all rows with T1.RPT_YR < '2002'

    Does not work.

    Thanks
    C

  3. #3
    Join Date
    Apr 2012
    Posts
    156
    You need it to create a result set before doing the delete. Give an order by a try, I am pretty sure that will work:

    DELETE FROM
    (SELECT RPT_YR
    FROM T1
    WHERE RPT_YR < '2002'
    order by rpt_yr
    FETCH FIRST 1000 ROWS ONLY
    )
    ;

  4. #4
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by cvcv View Post
    I just tried:

    DELETE FROM T1
    WHERE T1.RPT_YR < '2002'
    AND EXISTS
    (SELECT *
    FROM T2
    WHERE T2.RPT_YR = T1.RPT_YR
    AND T2.RPT_TCD = T1.RPT_TCD
    AND T2.RPT_DT = T1.RPT_DT
    AND T2.RC_CD = T1.RC_CD
    AND T2.SOFP_ID = T1.SOFP_ID
    FETCH FIRST 1000 ROWS ONLY
    )
    ;

    I thought it would delete 1000 rows only, but it deletes all rows with T1.RPT_YR < '2002'

    Does not work.

    Thanks
    C
    It does not work since the exists predicate will evaluate to true for every row that matches the outer predicates. The following should be ok:

    Code:
    DELETE FROM
    (SELECT RPT_YR
    FROM T1
    WHERE RPT_YR < '2002'
    FETCH FIRST 1000 ROWS ONLY
    )
    but apparently your platform and/or version - whatever that is - does not support this.
    --
    Lennart

  5. #5
    Join Date
    Sep 2013
    Posts
    7
    Ohh no

    Ok, thanks guys.

    I'm on the mainframe (cobol) and Db2V9

    Would you know if there is a workaround?

    Thanks
    C

  6. #6
    Join Date
    Apr 2012
    Posts
    156
    Did you try an order by per my previous comment. I believe that will work on the mainframe.

  7. #7
    Join Date
    Sep 2013
    Posts
    7
    Hi Azready, thanks, yes I tried it but I get the same error:

    SQLCODE = -104, ERROR: ILLEGAL SYMBOL "(".

    DELETE FROM
    (SELECT RPT_YR
    FROM T1
    WHERE RPT_YR < '2002'
    ORDER BY RPT_YR
    FETCH FIRST 1000 ROWS ONLY
    );

  8. #8
    Join Date
    Sep 2013
    Posts
    7
    Got it!!!

    Code:
    DELETE
    FROM T1 
    WHERE (RPT_YR
    ,RPT_TCD
    ,RPT_DT
    ,RC_CD
    ,SOFP_ID
    ) IN (SELECT RPT_YR
    ,RPT_TCD
    ,RPT_DT
    ,RC_CD
    ,SOFP_ID
    FROM T1
    WHERE RPT_YR < '2002'
    FETCH FIRST 1000 ROWS ONLY
    )
    ;


    thanks!
    C

Tags for this Thread

Posting Permissions

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