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!

score 21 · Answer 1 · edited Mar 20 '15 at 15:48

21

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)

edited Mar 20 '15 at 15:48

KarlP

5,149
2
28
41

answered May 08 '14 at 13:09

natasha.pecanova

211
2
2

This will translate into a subselect in sql rather neatly, and will work as long as the key is not a composite key. – KarlP Mar 20 '15 at 15:47
Yes! this worked. Damn! I was worried for a sec. Also this works if there is a composite embedded key – mike Aug 09 '16 at 14:41
1

Will this not result in the following error -- " You can't specify target table 'bid_response' for update in FROM clause'. I am getting the above error and this is consistent with what is reported on https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause?answertab=oldest#tab-top – HopeKing Jun 11 '17 at 11:03

score 9 · Accepted Answer · answered Jun 07 '11 at 12:04

9

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.

answered Jun 07 '11 at 12:04

Edwin Dalorzo

76,803
25
144
205

7

A native SQL update would be prefered then, it's silly to iterate over n entry in your database to make update when you can do it with one query! (and it's odd JPA doesn't include it?!) – Cyril N. Jun 07 '11 at 12:06
@cx42net Even so, I do not see the need of the join in you query. Why cant you just write the query over the Team entity without using the members field? After all you are using a left join, so the result would be the same, would it not? – Edwin Dalorzo Jun 07 '11 at 12:16
Member joins Team and Account together, and I need to filter the update based on accounts, so I guess I need to join Member. – Cyril N. Jun 07 '11 at 12:23

Update value with join

2 Answers2

Linked