I need to delete n rows from a table. I had retrieved data using

select * from table_name fetch first 100 rows only

Now,
I need to delete the same data.
I tried,

delete from (select * from table_name fetch first 100 rows only).

It doesn't work. Any suggestions. Thanks in advance.

Note: I need to get data from a table, then run a transformation using an ETL tool (Informatica) and then delete the source data once the process is complete.

DB2 for os 390 v8

I am not sure whether DB2 for z/OS V8 supports this, but you can try this (it uses the EMP table in the DB2 LUW sample database):

DELETE from EMP A WHERE (A.EMPNO) in
(SELECT T.EMPNO FROM
(SELECT ROW_NUMBER() OVER (ORDER BY EMPNO) AS ROWNUM, EMPNO FROM EMP) AS T
WHERE ROWNUM < 11);

Note that the ORDER BY causes the first 10 rows to be deleted according to the sequence of the EMPNO, and not the physical sequence of the rows (which could be in random order). Obviously, you could order the rows by some other other column.

Thanks for the response, but did try that... i get an error... Any suggestions..Would really appreciate it..

Han

Check the SQL Reference to see if z/OS supports ROW_NUMBER() OVER. If not, you can create a temporary table of the keys you want deleted.

If it is supported, posted your exact SQL and the exact error message.