Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2006
    Posts
    3

    Unanswered: Need help to delete N rows from a table

    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.

  2. #2
    Join Date
    Oct 2006
    Posts
    3

    version no. DB2 for os390 v8

    DB2 for os 390 v8

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Oct 2006
    Posts
    3

    I did try that..

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

    Han

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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