I’m working on a project that utilizes JPA/Hibernate as its ORM. I was writing a few named queries for an JPQL/HQL delete and was getting an SQLGrammarException. I soon found out that I couldn’t use inner joins on a delete statement, even at the SQL level. Here’s the query transformation:
I originally had this query:
@NamedQuery(name = Queue.REMOVE_ROWS_OF_EARLY_RELEASE_REASON, query = "delete from Queue q " + "where q.enabledMember = :enabledMember " + "and q.letter.eventReason.event.type = :eventType " + "and q.letter.eventReason.reason = :reason ")
It had to evolve to this:
@NamedQuery(name = Queue.REMOVE_ROWS_OF_EARLY_RELEASE_REASON, query = "delete from Queue q " + "where q in (select sq from Queue sq " + " where sq.enabledMember = :enabledMember " + " and sq.letter.eventReason.event.type = :eventType " + " and sq.letter.eventReason.reason = :reason )"
The only solution that I could think of in order to keep the filter that I need was to use a subquery. Anyone know of any other awesome solutions?