Why does this HQL delete fail, when an HQL select with same terms works?

Thomas Bratt picture Thomas Bratt · Oct 5, 2010 · Viewed 8k times · Source

Why does the following HQL query fail?

string hql = @"delete MyLog log
               where
                    log.UtcTimestamp < :threshold and
                    log.Configuration.Application = :application";

session.CreateQuery(hql)
       .SetDateTime("threshold", threshold)
       .SetEnum("application", this.application)
       .ExecuteUpdate();

The same form of query works when used in a select:

string hql = @"from MyLog log
               where
                    log.UtcTimestamp < :threshold and
                    log.Configuration.Application = :application";
IList<MyLog> log = session.CreateQuery(hql)
    .SetDateTime("threshold", threshold)
    .SetEnum("application", this.application)
    .List<MyLog>();

The mapping for MyLog contains:

References(x => x.Configuration)
     .Columns("CONFIGURATION_ID")
     .ReadOnly();      

The mapping for Configuration contains:

Map(x => x.Application, "APPLICATION_ID");

The error I get is:

delete from MYLOG, CONFIGURATION countercon1_ where UTC_TIMESTAMP<:p0 and APPLICATION_ID=:p1; :p0 = 04/10/2010 17:15:52, :p1 = 7

NHibernate.Exceptions.GenericADOException: could not execute update query [SQL:

delete from MYLOG, CONFIGURATION countercon1_ where UTC_TIMESTAMP< ? and APPLICATION_ID= ?

] ---> Oracle.DataAccess.Client.OracleException: ORA-00933: SQL command not properly ended

Answer

Diego Mijelshon picture Diego Mijelshon · Oct 6, 2010

Try this:

delete MyLog log
where log.id in
          (select l.id
           from MyLog l
           where l.UtcTimestamp < :threshold and
           and.Configuration.Application = :application)