How to remove only the top 100 lines?

Question

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

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

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

Answer 1

Unfortunately, you cannot restrict the use of HQL and query.setMaxResults(number) will not work with UPDATE or DELETE s. Your options:

• iterate / load each object and delete it. Ugh!
• write two separate HQL queries where the results of the first query are passed to another query (which does the actual deletion). It may or may not be possible in your situation.
• use raw SQL / JDBC.
• make two requests: the first request only for identifiers (without loading the actual objects) with a limited set of results (100 in your case). The second query will delete WHERE someid IN (id1, id2, ..., id100)

Answer 2

No, unfortunately, it is impossible. For this, depending on your use case, I would study if it is worthy of either of the following items: 1 to select with setMaxResults , and then delete items with delete (poor performance) 2 to use session.createSQLQuery , creating SQL and you can use limit