Search code examples
jpajoinjpql

UPDATE and JOIN with JPQL

Tutorials and samples about JPQL always deal with SELECT statement and sometimes, simple UPDATE statements. I need to update a table with a join.

I have simplified my env :

KEY
= id
- counter

APPLET
= id
! key_id (1-1)

DEVICE
= id
! applet_id (1-1)
! user_id (1-n)

USER
= id
- login

A device has a unique applet, which has a unique keyset. But an user can own several devices.

I need to reset the counter of every KEY attached to the USER login "x".

I tried some syntax with UPDATE and JOIN, without success. Any clue ?

Thank you.

Solution

  • What did you try and what error did you get? What is your object model?

    Perhaps something like,

    Update Key k set k.counter = 0 where exists (Select u from User u join u.devices d where u.login = "x" and d.applet.key = k)

    See, http://en.wikibooks.org/wiki/Java_Persistence/JPQL_BNF#Update

    You could also select the objects and reset the counter in memory and commit the changes.