Q
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.


 

This was last published in September 2004

Dig Deeper on IBM DB2 management

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchAWS

SearchContentManagement

SearchOracle

SearchSAP

SearchSQLServer

Close