Delete query with MAX in HQL

Question

I have tried to use almost all HQL strings for this delete, I can not find out where the problem is. My last try was:

    final String deleteString = "delete Foo l where l.id < (max(id) from l)";

    final Query query = this.getEntityManager().createQuery(deleteString);

    final int deleted = query.executeUpdate();

I am getting this:

org.hibernate.hql.ast.QuerySyntaxException: unexpected token: from near line 1, column 73 [delete eu.unicorn.ctds.entity.Foo l where l.id < (max(id) from l)

Where could be the problem?

Answer 1

Missing select before max. try this

final String deleteString = "delete Foo l where l.id < ( select max(id) from l)";

After correcting query as above, you will get another error while execution saying "you can't specify target table 'Foo' for update/delete in FROM clause" which means you cannot delete the row in Foo while selecting a max from the same table Foo. So you have to do 2 queries. First query to select the max id and use that id in delete query.