HQL - Delete with JOIN Error

Question

I am trying to do an HQL removal with an introduction. After a quick search, I found that I need to create a request in the same way as suggested HERE:

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

This is my request:

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 indicates that the second (incorrect, one immediately after the "SELECT id FROM"

EDIT I tried so much like these same errors:

  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 : Such a request does not work due to the problem described in the link I 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())); 

Error:

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

DECISION

Having tried everything here, our conclusion:

• We cannot have only one query directly, because we do not have joins on DELETE.
• We cannot have only 2 queries (one subquery), because we have BUG MYSQL (described in the link)
• We cannot have 3 queries (2 subqueries) because we do not have a subquery in the FROM clause. This is why our second request does not work (select * from (select ...)) 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

DECISION

Having tried everything here, our conclusion:

• We cannot have only one query directly, because we cannot have joins in DELETE.

• We cannot have only 2 queries (one subquery), because we have MYSQL BUG (described by the provided link)

• We cannot have 3 queries (2 subqueries), because we cannot have a subquery in the FROM clause. Therefore, our second request does not work (select * from (select ...)) and 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())); 

Answer 2

Why do you need to use in (select in the same table from which you are deleting? Can you just put a 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 mean with the classDiscipline.clazz.id parameter? Is classDiscipline any other object with a field named clazz , which is another object? This is what the query is talking about.

Answer 3

The problem with your original request is that you are not allowed to have internal selections anywhere except in the select and where sections. Because HQL works in terms of entities, not tables or columns, selecting from a subset of tables does not make sense. See here :

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

We had a similar problem in our project, and for a long time I tried to find one solution for queries, but I am afraid that MySQL is not possible due to the design of the HQL language.

The best I came up with is to first get the identifiers as a list, and then pass that list as a list parameter for your update. For you, it could 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 4

there may be a situation where X is wrong

and you interrupt x instead of the column name (set ***column_name*** with the corresponding 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 sibaker does not work in sleep mode, try with a simple connection request

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