Using a join in a Hibernate HQL update query

hibernate, hql, join, nhibernate

Solution

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.

Problem

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?

Original source