Update value with join

Question

using Hibernate, I'd like to update a data in the database based on conditions, but I got the following error : "node to traverse cannot be null"

Here is my database description :

Account: id, email, password
Member : id, account, team
Team: id, current (and a reference to member => members)

Here is my JPA :

UPDATE Team t SET t.current = :current LEFT JOIN t.members m WHERE t.current = :current_true AND m.account = :account

What am I doing wrong? If i move the LEFT JOIN to before the SET :

UPDATE Team t LEFT JOIN t.members m SET t.current = :current WHERE t.current = :current_true AND m.account = :account

I got : "expecting SET, found LEFT"

If I remove the join :

UPDATE Team t SET t.current = :current WHERE t.current = :current_true AND t.members.account = :account

I got : "Illegal attempt to dereference collection".

What is the correct way to update values ?

Thanks for your help!

Answer 1

The JPA 2.0 specification in chapter 4 contains details of all supported features in JPQL. This is the definition of the "update" statement:

The syntax of these operations is as follows:

update_statement ::= update_clause [where_clause]
 update_clause ::= UPDATE entity_name [[AS] identification_variable] 
                     SET update_item {, update_item}*
  update_item ::= [identification_variable.]{state_field | single_valued_object_field} =
                     new_value 
new_value ::= 
   scalar_expression |
   simple_entity_expression |
   NULL

As you can see, support for multiple entities is not stated here. I guess you will have to find a different way to do it, perhaps create a method that selects the entities that you want to update first, and then iterate over the results setting the values. Or you could use a native SQL update.

Answer 2

Use a subquery:

(not tested)

UPDATE Team t SET t.current = :current 
WHERE t.id in (select t1.id from Team t1  LEFT JOIN t1.members m WHERE t1.current = :current_true AND m.account = :account)