Inner Joins in JPQL Delete Statements
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?