How can I limit the number of rows updated in a JPQL query?

Question

I want to limit this update query to only update 5 rows:

Query updateQuery = em.createQuery("update Entity e SET e.myVar = 1");
updateQuery.setMaxResults(5).executeUpdate();

setMaxResults does not seem to do the job. How can I do this in jpql?

Answer 1

If portability is not a issue, then you can go for database specific native query.

• Oracle : em.createNativeQuery("update TableName SET myVar = 1 where id IN (SELECT id FROM TableName WHERE ROWNUM <= 5)").executeUpdate();

• MySql : em.createNativeQuery("update TableName SET myVar = 1 where id IN (SELECT id FROM TableName LIMIT 5)").executeUpdate();

ROWNUM & LIMIT are used to limit number of results in a query for Oracle & MySql respectively.

Haven't mentioned which database you are using. Provided sample-code might help you, make alterations accordingly.