Update using join or nasted sub-query Hibernate (HQL)

Question

I have a problem creating HQL that updates entity CommitteeMembership with nested queries or using joins, i tried this query first:

update CommitteeMemberShip cms set cms.isDeleted=1 
where cms.committeeCycle.committee.id=:committeeId

but the generated SQL was wrong as following:

update CommitteeMemberShip cross join  set isDeleted=1 where committee_id=?

without any this after "cross join" makes Hibernate throws SQLGrammarException

After that, i have changed my query to use sub-query:

update CommitteeMemberShip cms set cms.isDeleted=1 
where cms.id in (select cmsIn.id from CommitteeMemberShip cmsIn inner join 
cmsIn.committeeCycle cc inner join cc.committee c where c.id=:committeeId)

now Hibernate throws

java.sql.SQLException: You can't specify target table 'CommitteeMemberShip' for 
update in FROM clause

any one have any idea how i can write this update query in HQL??

score 0 · Answer 1 · answered Apr 01 '14 at 13:36

0

Try this

update CommitteeMemberShip cms set cms.isDeleted=1 where cms.id = some(select cmsIn.id from CommitteeMemberShip cmsIn inner join cmsIn.committeeCycle cc inner join cc.committee c where c.id=:committeeId)

answered Apr 01 '14 at 13:36

Fanooos

2,718
5
31
55

I think this will do exactly what the second try of sub-query in question, it throws: java.sql.SQLException: You can't specify target table 'CommitteeMemberShip' for update in FROM clause – Ahmed Adel Apr 01 '14 at 13:43
I am not sure but try this update CommitteeMemberShip cms join CommitteeMemberShip cms2 on cms.id=cms2.id set cms.isDeleted=1 where cms2.committeeCycle.committee.id=:committeeId – Fanooos Apr 01 '14 at 13:51
You cannot write join after the entity name or alias, it throws org.hibernate.hql.ast.QuerySyntaxException: expecting "set", found 'join' – Ahmed Adel Apr 01 '14 at 14:09

Shaam · Answer 2 · 2016-08-22T12:52:10.263

0

Try this "UPDATE table1 r SET r.column = @value WHERE r.column IN("SELECT rd.Id FROM r.table2 rd WHERE rd.Column='"+@value+"' AND rd.Column='"+value+"'";

edited Aug 22 '16 at 12:52

answered Aug 22 '16 at 12:19

Shaam

143
11

Lukas Eder · Answer 3 · 2022-11-23T14:11:03.633

It seems that HQL does not yet support path expressions / implicit joins in DML statements. Here's how jOOQ does it (in SQL, assuming all paths are to-one relationships (Disclaimer: I work for the company behind jOOQ)):

UPDATE CommitteeMemberShip cms
SET cms.isDeleted = 1
WHERE (
  SELECT Committee.id
  FROM Committee
  WHERE (
    SELECT CommitteeCycle.committeeId
    FROM CommitteeCycle
    WHERE CommitteeCycle.id = cms.committeeCycleId
  ) = Committee.id 
) = :committeeId

Your own approach didn't work because you repeated the CommitteeMemberShip entity unnecessarily. That's a known MySQL limitation, see: MySQL Error 1093 - Can't specify target table for update in FROM clause

This would have worked, I suspect?

UPDATE CommitteeMemberShip cms
SET cms.isDeleted = 1
WHERE cms.committeeCycleId IN (
  SELECT CommitteeCycle.id
  FROM CommitteeCycle cc
  JOIN cc.committee c
  WHERE c.id = :committeeId
)

Starting with Hibernate 6 and their support for derived tables, you might be able to work around MySQL's limitation like this (the same way as jOOQ does it out of the box, see here):

UPDATE CommitteeMemberShip cms 
SET cms.isDeleted=1 
WHERE cms.id IN (
  -- Wrap the query once more in a derived table
  SELECT t.id
  FROM (
    SELECT cmsIn.id 
    FROM CommitteeMemberShip cmsIn 
    JOIN cmsIn.committeeCycle cc 
    JOIN cc.committee c WHERE c.id=:committeeId
  ) t
)

Update using join or nasted sub-query Hibernate (HQL)

3 Answers3