HQL - Delete with JOIN error
<p>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:</p>

<p><a href="http://dasunhegoda.com/1093-you-cant-specify-target-table-table_name-for-update-in-from-clause/104/" rel="noreferrer">http://dasunhegoda.com/1093-you-cant-specify-target-table-table_name-for-update-in-from-clause/104/</a></p>

<p>This is my query:</p>

<pre><code>    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()));
</code></pre>

<p>But I keep getting this error:</p>

<pre><code>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 )]
</code></pre>

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

<p><strong>EDIT</strong> I ve tried like this and same error occours:</p>

<pre><code>    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 )",
</code></pre>

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

<pre><code>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()));
</code></pre>

<p>The error is:</p>

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

<h1>SOLUTION</h1>

<p>After trying everything out here's our conclusion:</p>

<ul>
<li>We cant have only one query directly because we cant have joins on DELETE.</li>
<li>We cant have only 2 queries (one subquery) because we have a MYSQL BUG (described on the link provided)</li>
<li>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 <code>(select * from (select ...))</code> is invalid.</li>
</ul>

<p>So I decided to use NativeSQL to solve the problem:</p>

<pre><code>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()));
</code></pre>

<p>Special thanks to @scaisEdge</p>