3

This is an extension of this question Update Statement with JOIN in SQL but I am trying to use Spring Data JPQL.

I am trying to use Update along with JOIN in JPQL as follows

@Modifying
@Query("UPDATE TotalValue tv JOIN LineItems li WHERE li.totalValue.totalValueId=:totalValuedId SET tv.totalAmount =sum(li.itemTotalValue) ")
void calculateTotalAmount(@Param("totalValuedId") Long totalValuedId);

However, i get an error as follows

org.hibernate.hql.internal.ast.QuerySyntaxException: expecting "set", found 'JOIN'

Is UPDATE and JOIN not possible in JPQL ? What is the alternative. Thanks

Community
  • 1
  • 1
HopeKing
  • 3,317
  • 7
  • 39
  • 62

1 Answers1

8

The JPQL syntax seems to indicate that a JOIN is actually not possible in an UPDATE statement.

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
  • Many Thanks. I suspected as much. Is the alternative only a native query ? – HopeKing Jun 19 '17 at 04:35
  • Often it is possible to formulate the update without an explicit join, just with property navigation, but I'd need to see the relevant part of the entities to be sure. Otherwise, yes I think a native query would be applicable. – Jens Schauder Jun 19 '17 at 04:47
  • Thanks for your help. – HopeKing Jun 19 '17 at 11:41