I'm struggling to write a rather complex JPQ query, I'm really not sure how the syntax goes for this. Is it MySQL or SQLServer syntax? Here is what I have (censored):

UPDATE FirstTable a INNER JOIN SecondTable b on a.secondTableId = b.secondTableId
SET a.firstItem = ?1 a.secondItem = ?2 b.firstItem = ?3 b.secondItem = ?4
b.thirdItem = ?5 b.fourthItem = ?6 a.thirdItem = ?7 a.fourthItem =?8
WHERE a.ID = ?9 AND a.environment = ?10

The variables are laid out underneath but the part I need help with is just the syntax.

Thanks in advance.

I'm struggling to write a rather complex JPQ query, I'm really not sure how the syntax goes for this. Is it MySQL or SQLServer syntax? Here is what I have (censored):

UPDATE FirstTable a INNER JOIN SecondTable b on a.secondTableId = b.secondTableId
SET a.firstItem = ?1 a.secondItem = ?2 b.firstItem = ?3 b.secondItem = ?4
b.thirdItem = ?5 b.fourthItem = ?6 a.thirdItem = ?7 a.fourthItem =?8
WHERE a.ID = ?9 AND a.environment = ?10

The variables are laid out underneath but the part I need help with is just the syntax.

Thanks in advance.

• java
• sql
• hibernate
• jpa
• jpql

Share Improve this question Follow asked Mar 13 at 21:37 No longer in collegeNo longer in college 3766 bronze badges 1

• Can you provide more code how do u plan to use the query? – Nico S. Commented Mar 14 at 0:20

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

JPQL does not support JOIN in UPDATE queries. This is because JPQL allows to update only a single entity at a time. So the there is no problem with your syntax, such query is simply impossible withing JPQL.

In spite of that you could split the query into two separate methods in your repository class. For example

@Modifying
@Query("UPDATE FirstTable a SET a.firstItem = ?1, a.secondItem = ?2, a.thirdItem = ?7, a.fourthItem = ?8 WHERE a.id = ?9 AND a.environment = ?10")
void updateFirstTable(String firstItem, String secondItem, String thirdItem, String fourthItem, Long id, String environment);


@Modifying
@Query("UPDATE SecondTable b SET b.firstItem = ?3, b.secondItem = ?4, b.thirdItem = ?5, b.fourthItem = ?6 WHERE b.secondTableId = (SELECT a.secondTableId FROM FirstTable a WHERE a.id = ?9 AND a.environment = ?10)")
void updateSecondTable(String firstItem, String secondItem, String thirdItem, String fourthItem, Long id, String environment);

After that call the two methods in Service class within the same transaction and be done.

@Transactional
public void updateTables(String firstItem, String secondItem, String thirdItem, String fourthItem,
                         String firstItemB, String secondItemB, String thirdItemB, String fourthItemB,
                         Long id, String environment) {
    repository.updateFirstTable(firstItem, secondItem, thirdItem, fourthItem, id, environment);
    repository.updateSecondTable(firstItemB, secondItemB, thirdItemB, fourthItemB, id, environment);
}

You didn't provide any context of the query point and usage but in my opinion it's better to split the query into two as it's more likely that there will be a situation where you need to update the first table while preserving the values of the first one. If such situation is impossible then there might be some design issues.