How do I delete only the top 100 rows?

Question

I have 100,000 rows to delete in DB2. I use Hibernate (HQL) something like delete from than query.executeQuery().

Is it possible in HQL to limit the number of rows to delete? For example:

query.setMaxRowTodelete(100); //  this is just an example.
query.executeQuery();

Answer 1

Saddly, you can not limit with HQL and query.setMaxResults(number) won't work with UPDATEs or DELETEs. Your options are:

• iterate over/load each object and delete it. Yuck!
• write two separate HQL queries where the results of the first query are fed into another query (which does the actual delete). Might or might not be possible in your situation.
• use raw SQL/JDBC.
• make two queries: first query on IDs only (without loading actual objects) having limited result set (100 in your case). Second query would delete WHERE someid IN (id1, id2, ..., id100)