Page 1 of 1
Query to Delete First 1000 Records from a Table 'DB2 on Z/OS'
Posted: Mon Jun 20, 2016 1:44 pm
by 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,
Re: Query to Delete First 1000 Records from a Table 'DB2 on Z/OS'
Posted: Mon Jun 20, 2016 2:30 pm
by zprogrammer
First 10,000 rows mean what? Is that you wanted to delete the rows that were inserted first?
Re: Query to Delete First 1000 Records from a Table 'DB2 on Z/OS'
Posted: Mon Jun 20, 2016 4:54 pm
by Anuj Dhawan
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".
Re: Query to Delete First 1000 Records from a Table 'DB2 on Z/OS'
Posted: Mon Jun 20, 2016 5:11 pm
by 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.
Re: Query to Delete First 1000 Records from a Table 'DB2 on Z/OS'
Posted: Mon Jun 20, 2016 5:15 pm
by 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?