#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Posts
    5
    Rep Power
    0

    Unhappy DB2: How to delete using fetch first


    Situation : Need to delete first 10 rows at a time from a set of rows that are returned

    Solutions available :
    1) Fetch first as part of select subquery
    2) Using ROW_NUMBER OVER...some column name
    3) Select partial reocrd usign subquery

    Analysis : I can't use (3)

    Looking for solution for deleting the records using "fetch first N rows only" clause

    DELETE FROM TABLE_NAME WHERE COL_NAME IN (SELECT COL_NAME FROM TABLE_NAME FETCH FIRST 'N' ROWS ONLY)

    ERROR: db2 => delete * from staff where id in (select id from staff order by id fetch f
    irst 1 row only)
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "delete" was found following
    "BEGIN-OF-STATEMENT". Expected tokens may include: "SELECT". SQLSTATE=42601
  2. #2
  3. No Profile Picture
    Clueless llama
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Feb 2001
    Location
    Lincoln, NE. USA
    Posts
    2,353
    Rep Power
    117
    I believe the asterisk is not supposed to be there. Try

    delete from staff where id in (select id from staff order by id fetch first 1 row only)
    ~Nemi

    Before posting did you try:
    [ Javadocs | Google ]
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Posts
    5
    Rep Power
    0

    DB2: How to delete using fetch first


    Yeah, i tried without "*" symbol only...in first place..(typo)


    The problem I found is that its availabel only in version higher than 8+ ..so its solved.

    By the way, do u knwo how to use Block Cursors for deletion?
  6. #4
  7. No Profile Picture
    Clueless llama
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Feb 2001
    Location
    Lincoln, NE. USA
    Posts
    2,353
    Rep Power
    117
    Nope, sorry.
    ~Nemi

    Before posting did you try:
    [ Javadocs | Google ]
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Posts
    5
    Rep Power
    0

    Deletion using Cursors


    Is there any BEST way to delete set of records at a time using cursors?

    DB2 Version = 7.2.5 on AIX
    Embedded SQL on C++
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Location
    Pretoria, South Africa
    Posts
    3
    Rep Power
    0

    Cursor


    Try this:

    BEGIN NOT ATOMIC
    FOR REC AS C1 CURSOR WITH HOLD FOR
    SELECT 'TEST' col1,
    ROW_NUMBER() OVER() as ROWNUM
    FROM TABLE
    DO
    DELETE FROM TABLE WHERE PKEY = COL1;--
    IF MOD(ROWNUM,10000) = 0 THEN commit;--
    end if;--
    END FOR;--
    commit;--
    END;


    Originally Posted by anaveens
    Is there any BEST way to delete set of records at a time using cursors?

    DB2 Version = 7.2.5 on AIX
    Embedded SQL on C++

IMN logo majestic logo threadwatch logo seochat tools logo