0

I have an update query in JPA/ Hibernate like this:

@Modifying
@Query(value = "UPDATE dogs d " +
        "SET d.active = 0 " +
        "WHERE d.id IN (:dogIds) AND d.active = 1",
        nativeQuery = true)
void softDeleteDogs(List<Long> dogIds);

I was wondering if having a large number of items in the dogIds list will cause an issue in MySQL when executing a query (and thus, maybe I would need to paginate that list instead). Any ideas?

hypercube
  • 958
  • 2
  • 16
  • 34
  • 3
    As far as I know there is no limit in JPA, but there is definitely a limit in underlying databases. You can use either batching (split single query into multiple queries with smaller `IN` chunks) or you can use temporary tables - insert whole list of ids in such table and then rewrite your original UPDATE query so that instead of IN clause it would use the join with that temporary table. – Nikolai Shevchenko Jul 22 '21 at 09:07
  • you can also read this excellent answer https://stackoverflow.com/a/52363760/2224047 – Nikolai Shevchenko Jul 22 '21 at 09:14
  • Does this answer your question? [MySQL IN clause: max number of arguments](https://stackoverflow.com/questions/12666502/mysql-in-clause-max-number-of-arguments) – SternK Jul 22 '21 at 09:20
  • Yes, pretty much. – hypercube Jul 22 '21 at 11:55

0 Answers0