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,
Query to Delete First 1000 Records from a Table 'DB2 on Z/OS'
-
- Global Moderator
- Posts: 588
- Joined: Wed Nov 20, 2013 11:53 am
- Location: Mars
Re: Query to Delete First 1000 Records from a Table 'DB2 on Z/OS'
First 10,000 rows mean what? Is that you wanted to delete the rows that were inserted first?
zprogrammer
- Anuj Dhawan
- Founder
- Posts: 2806
- Joined: Sun Apr 21, 2013 7:40 pm
- Location: Mumbai, India
- Contact:
Re: Query to Delete First 1000 Records from a Table 'DB2 on Z/OS'
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.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 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)
Code: Select all
EXEC SQL
DELETE FROM table_name
WHERE CURRENT OF C1;
[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.
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.
Re: Query to Delete First 1000 Records from a Table 'DB2 on Z/OS'
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
Nic
- Anuj Dhawan
- Founder
- Posts: 2806
- Joined: Sun Apr 21, 2013 7:40 pm
- Location: Mumbai, India
- Contact:
Re: Query to Delete First 1000 Records from a Table 'DB2 on Z/OS'
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.
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.
Create an account or sign in to join the discussion
You need to be a member in order to post a reply
Create an account
Not a member? register to join our community
Members can start their own topics & subscribe to topics
It’s free and only takes a minute