|
View previous topic :: View next topic
|
| Author |
Message |
haidude
New User
Joined: 11 May 2007
Posts: 10
Location: Mumbai
|
|
|
|
| How to delete first 1000 in a db2 table,Please explain clearly? |
|
| Back to top |
|
 |
haidude
New User
Joined: 11 May 2007
Posts: 10
Location: Mumbai
|
|
|
|
| the above question is How to delete first 1000 records in a db2 table,Please explain clearly? |
|
| Back to top |
|
|
prasadvrk
Active User
Joined: 31 May 2006
Posts: 200
Location: Netherlands
|
|
|
|
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 |
|
|
sathish_rathinam
New User
.jpg)
Joined: 22 Aug 2005
Posts: 59
Location: india
|
|
|
|
| can we use DELETE FROM TABLENAME WHERE ROWNUM <=100 |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
You might use it, but you might not get the results you want/expect. . . . |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
| You can't use that unless ROWNUM is defined as a column in the table. |
|
| Back to top |
|
|
|
|
