Hibernate HQL update with a subselect in set clause

Question

Hi all,

I'm trying to do an update in hibernate HQL with a subselect in a set clause like:

update UserObject set code = (select n.code from SomeUserObject n where n.id = 1000)

It isnt working, it is not supported?

Thanks

Udo

Answer 1

From the Hibernate documentation:

13.4. DML-style operations

...

The pseudo-syntax for UPDATE and DELETE statements is: ( UPDATE | DELETE ) FROM? EntityName (WHERE where_conditions)?.

Some points to note:

• In the from-clause, the FROM keyword is optional
• There can only be a single entity named in the from-clause. It can, however, be aliased. If the entity name is aliased, then any property references must be qualified using that alias. If the entity name is not aliased, then it is illegal for any property references to be qualified.
• No joins, 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.
• The where-clause is also optional.

While the documentation doesn't explicitly mentions a restriction about the set part, one could interpret that sub-queries are only supported in the where-clause. But...

I found an 4 years old (sigh) issue about bulk update problems (HHH-1658) and according to the reporter, the following works:

UPDATE Cat c SET c.weight = (SELECT SUM(f.amount) FROM Food f WHERE f.owner = c)

I wonder if using an alias in the from-clause would help. Looks like there is some weirdness anyway.