Using a join in a Hibernate HQL update query

Question

i have the following hibernate mapping:

 <class name="Domain.Roomreservation, Core" table="Reservationroom">
    <id name="ID" unsaved-value="undefined">
        <generator class="native">
            <!--<param name="sequence">GLOBALSEQUENCE</param>-->
        </generator>
    </id>

    <property name="FromTime" not-null="true" index="IDX_RESRAUM_FromTime" />
    <property name="UntilTime" not-null="true" index="IDX_RESRAUM_UntilTime"/>

    <many-to-one name="Booking" column="Book_ID" index="IDX_RAUMRES_BOOK" lazy="false"
        class="Domain.Booking, Core" not-null="true" />
    </class>

And the Reservationroom table looks like:

ID         <pk>
Book_ID    <fk>
FromTime
UntilTime
....
....

My Hibernate Query looks like:

String hql = "UPDATE Roomreservation as rr set rr.FromTime= 12:15" +
                     "Inner Join Booking b ON rr.Book_ID= b.ID " +
                     "Where b.ID = 95637";
                        IQuery query = CurrentSession.CreateQuery(hql);
                        int result = query.ExecuteUpdate();
                        Debug.WriteLine("Rows affected: " + result);

But I always getting Error: NHibernate.Hql.Ast.ANTLR.QuerySyntaxException

Can someone help me how to get this to work?

score 29 · Accepted Answer · answered Aug 20 '12 at 08:41

29

You have to use subquery instead of join. Roughly as follows:

UPDATE Roomreservation as rr set rr.FromTime= 12:15
WHERE rr.Book_ID IN (
  SELECT b.id 
  FROM Booking b 
  WHERE b.id = 95637);

Additionally depending about type of FromTime it is likely that it should be presented in some other format.

answered Aug 20 '12 at 08:41

Mikko Maunu

41,366
10
132
135

1

isn't there any natural way to do ti without sub query and what about be the differences between the two ? – Adelin May 09 '15 at 22:39
@Adelin, it may be possible if you're using Mapping Annotations in Hibernate. – Shawn Apr 26 '18 at 20:43

score -5 · Answer 2 · answered Aug 20 '12 at 08:34

-5

This missing single quote for rr.FromTime= '12:15' should be the error.

String hql = "UPDATE Roomreservation as rr set rr.FromTime= '12:15' " +
"Inner Join Booking b ON rr.Book_ID= b.ID " +
"Where b.ID = 95637";

Can you please check this.

answered Aug 20 '12 at 08:34

Ramaraj Karuppusamy

2,350
5
24
35

rr.FromTime= '12:15'" + "Inner For this, we need to give space also rr.FromTime= '12:15'" + " Inner – Ramaraj Karuppusamy Aug 20 '12 at 08:41
4

Hibernate doesn't support Join on Update/Delete statement. – Sanghyun Lee Nov 11 '14 at 13:20

Using a join in a Hibernate HQL update query

2 Answers2

Linked