3

How can I limit the number of rows updated in hql. I know Query#setMaxResults() can be used for limiting records in select clause, but it is not working for update queries.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Dinoop paloli
  • 633
  • 2
  • 8
  • 25

2 Answers2

3

I'm not aware of any functionality to limit the number of records to update with SQL or any other query language. What you do normally is to have a more specific query on what to update. As you can't specify any order for update queries, what would be your criteria to decide on which records are updated? The update would be totally undefined in terms of what would be updated.
Or do you want to limit the number of records returned after an update?

philnate
  • 1,506
  • 2
  • 21
  • 39
  • In Mysql it is possible to limit the number of records affected by using limit. If I use limit=2 , then only two records will be get affected. – Dinoop paloli Feb 04 '16 at 09:43
  • Ok, so mysql supports this, but as this isn't a general feature of SQL, hibernate probably won't support it. have you tried to use something like this query http://stackoverflow.com/a/12620023/1377224, specifying what to update with a IN clause? – philnate Feb 04 '16 at 09:48
  • When updating a table , we cannot specify the same table in subquery within where clasue (at least in mysql). `update WALLET_HISTORY set IS_CREDITED=1 where ID=(select ID from WALLET_HISTORY limit 1)` throws exception , `Caused by: java.sql.SQLException: You can't specify target table 'WALLET_HISTORY' for update in FROM clause` – Dinoop paloli Feb 04 '16 at 09:55
  • But with the link posted it seems to work, if it's nested twice. If it's not working, then you need to do a select with a prequel query to gather the Ids you want to update with your second query. Might not be as sexy as a subquery, but will do the job just as fine. – philnate Feb 04 '16 at 09:59
  • Yeah, I thought of doing the same, but it wont become atomic because there can be a chance of any other thread already done the same for that record. – Dinoop paloli Feb 04 '16 at 10:00
  • First you could turn off auto-commit to have both queries ran as one transaction, if it's not idempotent. But more importantly, if your update is idempotent, what would be the problem? At worst you wouldn't update your limit of records. – philnate Feb 04 '16 at 10:09
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/102577/discussion-between-dinoop-paloli-and-philnate). – Dinoop paloli Feb 04 '16 at 10:15
  • @philnate - Updating the first x rows is sometimes legitimate - I have a case where I need to partition records for bulk processing in parallel, so I just want to mark the first x as batch1, the next x as batch2 and so on. (I am lucky that the processing for each record does not clash with processing for other records; I also have a case where I am not lucky - in that case I have some interesting WHERE clauses!). – nsandersen Mar 21 '16 at 17:12
0

I have used named sql query to do the same. Since hibernate is not having support for limit clause in update query.

Dinoop paloli
  • 633
  • 2
  • 8
  • 25