Search code examples
jpqlopenjpa

OpenJPA/MySQL: modifying a table while the same table is used in the where clause

I want to execute a delete query via openJPA and mysql.

This mysql statement works fine:

delete from GENERIC 
 where PARENT_ID not in (select g2.ID from (select * from GENERIC) g2);

The basic element are a table GENERIC with columns ID and PARENT_ID

Mapping the GENERIC table to GenericEntity class, ID column to id member (of that class) and PARENT_ID column to parentId member, I tried this simple test:

entityManager.createQuery("delete from GenericEntity g1 where " +
   "g1.parentId not in " +
   "(select g2.id from (select * from GenericEntity) g2)"
).executeUpdate();

And I get this error:

org.apache.openjpa.persistence.ArgumentException: "Encountered "g1 . parentId not in ( select g2 . id from (" at character 36, but expected: ["(", "*", "+", ",", "-", ".", "/", ":", "<", "<=", "<>", "=", ">", ">=", "?", "ABS", "ALL", "AND", "ANY", "AS", "ASC", "AVG", "BETWEEN", "BOTH", "BY", "CASE", "CLASS", "COALESCE", "CONCAT", "COUNT", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "DELETE", "DESC", "DISTINCT", "ELSE", "EMPTY", "END", "ENTRY", "ESCAPE", "EXISTS", "FETCH", "FROM", "GROUP", "HAVING", "IN", "INDEX", "INNER", "IS", "JOIN", "KEY", "LEADING", "LEFT", "LENGTH", "LIKE", "LOCATE", "LOWER", "MAX", "MEMBER", "MIN", "MOD", "NEW", "NOT", "NULL", "NULLIF", "OBJECT", "OF", "OR", "ORDER", "OUTER", "SELECT", "SET", "SIZE", "SOME", "SQRT", "SUBSTRING", "SUM", "THEN", "TRAILING", "TRIM", "TYPE", "UPDATE", "UPPER", "VALUE", "WHEN", "WHERE", , , , , , , , , ]." while parsing JPQL "delete from GenericEntity g1 where g1.parentId not in (select g2.id from (select * from GenericEntity) g2)". See nested stack trace for original parse error.

I tried different variants, also replaced the delete by an update (to set a 'deleted' flag instead), but it seems to be a general problem to modify a table when this very table is used in the where clause.

I'd appreciate very much a hint, how to continue or a link to any helpful material. Thank you very much in advance!

Solution

  • After a lot of research, I have found a solution. It works fine to separate the select from the delete query and pass the list of IDs as a parameter to the delete statement:

    List<Integer> idList = entityManager
    .createQuery("select g.id from GenericEntity g",Integer.class)
    .getResultList();
entityManager
    .createQuery("delete from GenericEntity g where g.parentId not in (:idList)")
    .setParameter("idList", idList)
    .executeUpdate();