Problem solve Get help with specific problems with your technologies, process and projects.

Deleting N rows from a DB2 table

How do you delete N rows from a DB2 table? Also, how do you retrieve bottom N rows from a DB2 table without sorting the table on key?

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!

Editor's note: Do you agree with this expert's response? If you have more to share, post it in one of our .VO7aaqqaAFk.0@/search390>discussion forums.


Dig Deeper on IBM DB2 management

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.