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

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)