Query to Delete First 1000 Records from a Table 'DB2 on Z/OS'

[elmoro]

Hi,

In my database one table contains more than 70 million records, now I want to delete the records by selecting first 10,000 records one by one with a subselect from ..

Please tell me the query to delete first 10,000 rows from the table based on subquery between two tables.

Thanks & Regards,

[zprogrammer]

First 10,000 rows mean what? Is that you wanted to delete the rows that were inserted first?

[Anuj Dhawan]

In my database one table contains more than 70 million records, now I want to delete the records by selecting first 10,000 records one by one with a subselect from ..

Please tell me the query to delete first 10,000 rows from the table based on subquery between two tables.

In the first statement, you said, that you want to delete the data from 'a table'. With which I assumed that you wanted to write a CURSOR for DELETE but in the last statement you said that you want to use subquery and two tables are involved, however, you did not reveal the details to join to tables.

In general there are multiple ways of using DELETE using a sub-query. Some of them listed below. First one, based on some assumptions, should be close to what you need; others, on the other hand, can be used as example to create one per your need:

[ol][li]Example one:[/li][/ol]

Code: Select all

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

[ol]2. If you want to use CURSOR:[/ol]

Code: Select all

           EXEC SQL                 
                DELETE FROM table_name 
                WHERE CURRENT OF C1;

[ol]3. From employee table X, delete the employee who has the most absences[/ol]
[font=monospace]     [/font]

Code: Select all

         EXEC SQL DELETE FROM EMP X
              WHERE ABSENT = (SELECT MAX(ABSENT) FROM EMP Y
              WHERE X.WORKDEPT = Y.WORKDEPT);

An aside: From an end-user's point of view, DB2 Tables are essentially like "tables" - and in that sense data is stored in rows and columns. Referring them as "records" is a loose term to refer to "rows".

[nicc]

Note that the first 10,000 rows returned are not guarantreed to be the same rows each time. For example, a reorg may affect the decision making as to which rows the DB2 engine returns.

[Anuj Dhawan]

One more thing - your post is in "Assembler & PL/I" part of the Forum, are you using any of these languages in relation to this topic?