HQL - Delete with JOIN error

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

Solution

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()));