HQL - Delete with JOIN error

Question

Im trying to execute an HQL delete with join.. After soon searching I found that I need to create a query just like suggested HERE:

http://dasunhegoda.com/1093-you-cant-specify-target-table-table_name-for-update-in-from-clause/104/

This is my query:

    dao.executeByHql(
        "DELETE FROM FinalGradeResult e WHERE e.id IN "
        + "( SELECT id FROM "
            + "( SELECT x FROM FinalGradeResult x " 
            + "where x.student.id = :studentId "
            + " AND x.classDiscipline IN " + 
                + "(SELECT cd from ClassDiscipline cd " 
                + " where cd.clazz.id = :clazzId ) ) as X )",
    new HqlParameter("studentId", student.getId()), 
    new HqlParameter("clazzId", from.getId()));

But I keep getting this error:

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token:( near line 1, column 94 [DELETE FROM xxxxxxxxxxxx.entity.FinalGradeResult e WHERE e.id IN ( SELECT id FROM ( SELECT x FROM xxxxxxxxxxxxxxx.entity.FinalGradeResult x where x.student.id = :studentId AND x.classDiscipline IN (SELECT cd from xxxxxxxxxxxxxxxx.entity.ClassDiscipline cd where cd.clazz.id = :clazzId ) ) as X )]

The error points out that the second ( is wrong, the one right after "SELECT id FROM"

EDIT I ve tried like this and same error occours:

    dao.executeByHql(
        "DELETE FROM FinalGradeResult e WHERE e.id IN "
        + "( SELECT id FROM "
        + "( SELECT x FROM FinalGradeResult x "
            + " where x.student.id = :studentId "
            + " AND x.classDiscipline.clazz.id = :clazzId )"
        + " as X )",

EDIT 2 : The query like this doesnt work because of the problem described on the link I've posted in this question:

dao.executeByHql(
        "DELETE FROM FinalGradeResult  e WHERE e.id IN " + "( SELECT x.id FROM FinalGradeResult as x "
                + " where x.student.id = :studentId " + " AND x.classDiscipline.clazz.id = :clazzId )",
        new HqlParameter("studentId", student.getId()), new HqlParameter("clazzId", from.getId()));

The error is:

Caused by: java.sql.SQLException: You can't specify target table 'tb_final_grade_result' for update in FROM clause

SOLUTION

After trying everything out here's our conclusion:

• We cant have only one query directly because we cant have joins on DELETE.
• We cant have only 2 queries (one subquery) because we have a MYSQL BUG (described on the link provided)
• We cant have 3 queries (2 subqueries) because we cant have a subquery in the FROM clause. That's why our second query doesn´t work (select * from (select ...)) is invalid.

So I decided to use NativeSQL to solve the problem:

dao.executeBySQL(
        "   delete from tb_final_grade_result where id in "
                + " (select * from ( select finalgrade1_.id from tb_final_grade_result finalgrade1_ cross join tb_class_discipline classdisci2_ "
                + " where finalgrade1_.id_class_discipline=classdisci2_.id and finalgrade1_.id_student= :studentId and classdisci2_.id_class= :clazzId ) as tmp )",
        new HqlParameter("studentId", student.getId()), new HqlParameter("clazzId", from.getId()));

Special thanks to @scaisEdge

Answer 1

Why do you need to use in (select on the same table that you are deleting from? Can't you just put the condition in the where clause?

DELETE FROM FinalGradeResult  e WHERE e.student.id = :studentId " + " AND e.classDiscipline.clazz.id = :clazzId )",
    new HqlParameter("studentId", student.getId()), new HqlParameter("clazzId", from.getId()));

Also, I'm not sure what you are referring to with the parameter classDiscipline.clazz.id? Is classDiscipline some other entity with a field named clazz that is yet another entity? That's what the query seems to be saying.

Answer 2

The problem with your original query is that you are not allowed you have inner selects anywhere but in select and where clauses. Because HQL operates in terms of entities and not tables or columns, selecting from subsets of tables doesn't make sense. See here:

Note that HQL subqueries can occur only in the select or where clauses.

We had a similar issue in our project and I struggled for awhile to find a single query solution, but I'm afraid with MySQL it isn't possible due to HQL's language design.

The best I came up with was to first fetch the ids as a list, and then pass that list as a list parameter to your update. For you it might be something like:

Query idQuery = createQuery("select id from FinalGradeResult gr where gr.student.id = :studentId AND gr.classDiscipline IN (SELECT cd from ClassDiscipline cd where cd.clazz.id = :clazzId"));
//add parameters
List<Number> ids = query.list();
Query entityQuery = createQuery("delete from FinalGradeResult where id in (:ids)");
entityQuery.setParameterList("ids", ids);

query.executeUpdate()

Answer 3

could be the position of as X is wrong

and you are repeting x instead of column name ( set ***column_name*** with the proper column name)

"DELETE FROM FinalGradeResult e WHERE e.id IN "
 + "( SELECT id  FROM 
    ( SELECT ***column_name ***  FROM FinalGradeResult x where x.student.id = :studentId AND x.classDiscipline IN 
    (SELECT cd from ClassDiscipline cd where cd.clazz.id = :clazzId ) as X)  )",

If the sibquery don't work in hibernate then try with a simple query with join

  DELETE FROM FinalGradeResult e 
  WHERE e.id in ( 
     SELECT  id FROM FinalGradeResult x 
     JOIN ClassDiscipline cd ON ( cd.cd.clazz.id = :clazzId  
                  and  x.classDiscipline = cd.ClassDiscipline )
     WHERE  x.student.id = :studentId);