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

HLASM for MVS. PL/I for MVS & Enterprise PL/I for z/OS.
Previous topicNext topic

Topic Author
elmoro
New Member
Posts: 1
Joined: Mon Jun 20, 2016 1:06 pm

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

Post by elmoro » Mon Jun 20, 2016 1:44 pm

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
Global Moderator
Global Moderator
Posts: 605
Joined: Wed Nov 20, 2013 11:53 am
Location: Mars
Zodiac: Libra

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

Post by zprogrammer » Mon Jun 20, 2016 2:30 pm

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


zprogrammer

User avatar

Anuj Dhawan
Founder
Posts: 2624
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Zodiac: Sagittarius

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

Post by Anuj Dhawan » Mon Jun 20, 2016 4:54 pm

elmoro wrote: 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".


Thanks,
Anuj

Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.


nicc
Global Moderator
Global Moderator
Posts: 597
Joined: Wed Apr 23, 2014 8:45 pm

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

Post by nicc » Mon Jun 20, 2016 5:11 pm

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.


Regards
Nic

User avatar

Anuj Dhawan
Founder
Posts: 2624
Joined: Sun Apr 21, 2013 7:40 pm
Location: Mumbai, India
Zodiac: Sagittarius

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

Post by Anuj Dhawan » Mon Jun 20, 2016 5:15 pm

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?


Thanks,
Anuj

Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.

Previous topicNext topic

Return to “Assembler & PL/I.”