0

I have defined a method on my JPARepository to update a property's entity for a given list of ids.

@Modifying
@Transactional
@Query("UPDATE Entity SET date = ?1 WHERE id IN (?2)")
void updateDeletionDate(Date date, List<Long> ids);

This works, but I've just found out that maximum length of the list is 1000 items (due to ORA-01795), so I'm trying the best approach I've found so far: 2. Use tuples. However, I don't know how to translate the query, since something like this obviously fails: UPDATE Entity SET date = ?1 WHERE (id , 0) IN ((?2, 0))

thmasker
  • 406
  • 1
  • 9
  • 21

1 Answers1

1

you could use a join on a subquery which selects the ids you want to update

UPDATE Entity e 
SET e.date = ?1 
WHERE e.id IN (SELECT i.id FROM (VALUES (?2), (?3), ...) as i(id))
kvz
  • 65
  • 8
  • could you explain a bit what the subquery does? – thmasker Jan 20 '23 at 11:49
  • 1
    the subquery is used to filter the rows of the Entity table based on the ids passed as parameters and the main query updates the date field for the rows returned by the subquery – kvz Jan 20 '23 at 11:58
  • I don't quite understand `(VALUES (?2), (?3), ...) as i(id)` – thmasker Jan 20 '23 at 12:02
  • 1
    this sintax is used to create a temporary inline table, The subquery selects the "id" column from this temporary table and then the outer query joins this temporary table with the Entities table on the "id" column and updates the "date" column of the matching rows with the new value passed in as the first parameter (?1). the subquery is then aliased as i(id) which means the subquery results are treated as a table with the name "i" and a single column named "id". – kvz Jan 20 '23 at 12:10
  • it does not compile... `Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 127 [UPDATE Entity e SET e.date = ?1 WHERE e.id IN (SELECT i.id (FROM (?2)) AS i(id)]` – thmasker Jan 20 '23 at 12:14