Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2011
    Location
    Frisco
    Posts
    8

    Unanswered: SQL delete function by RRN

    Found this on the web, i'm trying to delete records out of a file based on the RRN but nothing works. Why can't i just "delete from table where RN > ?" In my table, i'm wanting to delete every record beyond 4651.

    Do i have to do a fullselect, and what does the OVER function do?

    DELETE FROM
    (SELECT ROW_NUMBER() OVER(PARTITION BY USER_SID ORDER BY USER_SID)
    FROM
    BILL.TEST_USERS) AS TU(RN)
    WHERE
    RN=1

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    i'm wanting to delete every record beyond 4651
    Please try...
    Code:
    DELETE FROM
           (SELECT ROW_NUMBER() OVER(ORDER BY user_sid)
             FROM  bill.test_users) AS t(rn)
     WHERE rn > 4651
    ;
    (Removed "PARTITION BY USER_SID".)


    what does the OVER function do?
    See OLAP specifications
    OLAP specifications - IBM DB2 9.7 for Linux, UNIX, and Windows

  3. #3
    Join Date
    Oct 2011
    Location
    Frisco
    Posts
    8

    Reply to SQL Delete function

    I actually tried that and got a SQL0104 error. What is wrong with this code? And why do you have to use a subselect? Thanks
    delete

    from
    (select from row_number() over(order by atxpan)
    from
    jhcterry1.atpantrn) as t(rn)
    where
    rn > 4651

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please see the full text of SQL0104 error message.
    And review your code carefully.

    Your code includes extra "from" between "select" and "row_number()".

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    why do you have to use a subselect?
    See the delete statement itself.
    If you have any other idea, please post your idea.

  6. #6
    Join Date
    Oct 2011
    Location
    Frisco
    Posts
    8

    Reply to SQL Delete function

    still doesn't work. Is it something to do with the version of SQL i'm running perhaps?

    delete
    from
    (select row_number() over(order by atxpan)
    from
    jhcterry1.atpantrn) as t(rn)
    where
    rn > 4651

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    still doesn't work. Is it something to do with the version of SQL i'm running perhaps?
    No one might be able to help you with such too little and vague information.

    Please supply more information. At least, following item 1) and 2) are mandatory.

    1) DB2 version/release and platform OS.

    2) What means "doesn't work"?
    2-1) Got error message(s) at compile(prepare) time or executtion time?
    If so, Give us the error message(s) with full message text and codes.
    2-2) Or, were results different from your expectations?
    Supply test data, your received result, and your expected result.

    3) From what client software did you issued the delete statement.

    4) ...

Posting Permissions

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