HQL - complex update query

spljam · **Joined:** Thu Sep 18, 2008 7:39 am **Posts:** 6

Hi,

I've got problem to exceute one update-HQL-query.

There tree tables:

Code:

CRAETE TABLE CATEGORY_GROUP (
   CATEGORY_GROUP_ID, 
   NAME
) 

CRAETE TABLE PARAMETER_CATEGORY (
   PARAMETER_CATEGORY_ID, 
   NAME, 
   CATEGORY_GROUP_ID 
     REFERENCES( CATEGORY_GROUP .CATEGORY_GROUP_ID )
) 


CRAETE TABLE PARAMETER (
   PARAMETER_ID, 
   NAME, 
   VALUE, 
   PARAMETER_CATEGORY_ID, 
     REFERENCES( PARAMETER_CATEGORY.PARAMETER_CATEGORY_ID, )
)

I want to execute the following update query:

Code:

session.createQuery("UPDATE Parameter  p SET p.desc = ? " +
     "WHERE p.category.categoryGroup.name = ? ")
     .setParameter(0, "updated_value")
     .setParameter(1, "group_name").executeUpdate();

The hibernate generates sql query like this:

Code:

Hibernate: update PARAMETER, set VALUE=? where NAME=?

which is obviously wrong. Is it possibe to execute somehow update like this or hibernate does not have this functionality ??

jroop · **Joined:** Fri Jun 01, 2007 7:03 pm **Posts:** 3

See..
http://www.hibernate.org/hib_docs/v3/re ... irect.html

It appears that the bulk updating is limited to having only one class in the from clause. In your HQL you only have one class but you really need to join on your referenced objects (i.e. the Category and CategoryGroup which are present in the where clause). However, you can have joins in a subquery found in the where condition. You may want to try that avenue. Depending on how you have mapped your tables and classes, you may be able do something like this.

Code:

update Parameter
set value = :desc
where parameterId in (
  select p.parameterId
  from Parameter p
  join ParameterCategory pc
  join ParameterCategoryGroup pcg
  where pcg.name = :groupName
)