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

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)

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?

Nope, sorry.

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++

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;