Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to Delete first 1000 in a DB2 Table?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
haidude

New User


Joined: 11 May 2007
Posts: 10
Location: Mumbai

PostPosted: Tue Jun 12, 2007 10:12 am    Post subject: How to Delete first 1000 in a DB2 Table?
Reply with quote

How to delete first 1000 in a db2 table,Please explain clearly?
Back to top
View user's profile Send private message

haidude

New User


Joined: 11 May 2007
Posts: 10
Location: Mumbai

PostPosted: Tue Jun 12, 2007 10:18 am    Post subject:
Reply with quote

the above question is How to delete first 1000 records in a db2 table,Please explain clearly?
Back to top
View user's profile Send private message
prasadvrk

Active User


Joined: 31 May 2006
Posts: 200
Location: Netherlands

PostPosted: Tue Jun 12, 2007 10:53 am    Post subject:
Reply with quote

A quick search on net yielded this post ,hope your question is answered

First of all, you need to refresh your knowledge of "relational" DBMS. There really is no such thing as the "top" or "bottom" N rows in a table. With regard to the result set though, there is a top and a bottom.

You can use the FETCH FIRST N ROWS ONLY clause to retrieve only the first N rows, but to retrieve only the bottom N rows is a more difficult problem. For that, you would have to use scrollable cursors. A scrollable cursor allows you to move back and forth through the results set without first having to read/retrieve all of the rows before.

I suggest that you read up on scrollable cursors in the DB2 SQL Reference manual and the DB2 Application Programming manual. All DB2 manuals can be downloaded for free.

Basically, you would want to FETCH LAST from the scrollable cursor and then loop through with a FETCH PRIOR statement executing the loop N-1 times. That would give you the "bottom" N of any results set -- sorted or not.

As for your other question, I am confused as to why you would want to delete N rows from a table. Doesn't it matter what the data in the rows is? My guess is that you are asking how you would limit a delete to a subset of the rows that would apply to the WHERE condition of the DELETE. The answer is, you cannot. You would have to open a cursor with the same WHERE conditions specifying FOR UPDATE OF. Then you would FETCH and DELETE WHERE CURRENT OF cursor for that row in a loop that occurs N times. Of course, that means you have to write a program.

Hope this helps!
Back to top
View user's profile Send private message
sathish_rathinam

New User


Joined: 22 Aug 2005
Posts: 59
Location: india

PostPosted: Thu Jul 12, 2007 3:47 pm    Post subject: Re: How to Delete first 1000 in a DB2 Table?
Reply with quote

can we use DELETE FROM TABLENAME WHERE ROWNUM <=100
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jul 12, 2007 10:08 pm    Post subject:
Reply with quote

Hello,

You might use it, but you might not get the results you want/expect. . . .
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Fri Jul 13, 2007 3:39 am    Post subject:
Reply with quote

You can't use that unless ROWNUM is defined as a column in the table.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Data replication from multiple Db2 ta... kishpra DB2 1 Mon Mar 27, 2017 9:58 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts JCL to delete component in a package sundaram.naveen Compuware & Other Tools 14 Tue Nov 29, 2016 6:21 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us