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

Question

I want to limit this update request to only updating 5 lines:

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

setMaxResultsdoesn't seem to do the job. How to do this in jpql?

Answer 1

If portability is not a problem, you can ask for your own database 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 and LIMIT are used to limit the number of results in a query for Oracle and MySql, respectively.

Didn't mention which database you are using. If the sample code can help you, make the appropriate changes.