Self-referencing updates in HQL

Question

I have the following query in HQL:

update ProjectFile pf1 
set pf1.validUntil.id =123
where pf1 = (
select pf from ProjectVersion pv, ProjectFile as pf 
where pf.validFrom.sequence <= pv.sequence 
and pf.validUntil.sequence >= pv.sequence
and pf.state <> 12
and pf.projectVersion.project.id = 1
and pv.project.id = 1
and pv.id = 12
and pf.id not in (2,3,4)
)

Hibernate parses the query correctly and generates SQL, but the database (MySQL) fails with error:

You can't specify target table 'ProjectFile' for update in FROM clause

The problem seems to be that the table to be updated is queried in the same context. Is there any way to rewrite the HQL query to produce SQL that can be executed in MySQL correctly? The other approach would be to create an intermediate table, which is what exactly I am trying to avoid.

Answer 1

Hi,

I bumped into the same problem and posted a question here: MySQL/SQL: Update with correlated subquery from the updated table itself.

To solve your problem, you need to join at the UPDATE level, please take a look at the answer to my question.