Inner Joins in JPQL Delete Statements

| Comments

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:

1
2
3
4
5
@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:

1
2
3
4
5
6
@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?

Comments