CODEWITHSUNDEEP
mysqlhibernatehql
ntstha
ntstha

Reputation: 1173

Alternative way to use join in HQL update with Mysql

I am new to HQL and I need to do bulk update in HQL (no mysql query).

Here is a sample of my Entity

EnityA{
    int id,
    Date date,
    int criteria1,
    EntityB b;
}

EntityB{
   int id,
   status,
   List<EntityA> aas;
}

Now I want to update all As that has criteria1=1 and its corresponding EntityB status not equal to 6.

I just knew that hql doesn't support implicit or explicit joins, so there goes my thought of using them. Then I came across few blogs about using derived table. So I tried this query:

    update EntityA A set A.date= :someDate where A.id in 
(select temp.id from (select A1.id from EntityA A1 where A1.criteria=1 
and A1.b.status <> 6) as temp)

But unfortunately I am having unexpected token: ( exception.

I guess it is because of derived table also not supported by HQL. Is there any way now. I really need to check status of EntityB.

Thank You

Upvotes: 1

Views: 648

Answers (1)

Radim K&#246;hler
Radim K&#246;hler

Reputation: 123861

You are right, HQL for DML could use subqueries, and these can use joins. If do observe your query properly, it could be rewrittern like this, and that should be working:

 update EntityA A set A.date= :someDate where A.id in 
 // (select temp.id from 
 (select A1.id from EntityA A1 where A1.criteria=1 and A1.b.status <> 6) 
 // as temp)

other words:

 update EntityA A set A.date= :someDate where A.id in 
 (select A1.id from EntityA A1 where A1.criteria=1 and A1.b.status <> 6)

15.4. DML-style operations

  • ...
  • No Section 16.4, “Forms of join syntax”, either implicit or explicit, can be specified in a bulk HQL query. Sub-queries can be used in the where-clause, where the subqueries themselves may contain joins.

Upvotes: 1

Related Questions