Error deleting study on mysql

[prunk1al]

Describe the bug
When attempting to delete a Study using the StudyMgtRS.deleteStudy endpoint, an EJBException is thrown due to a GenericJDBCException. MySQL rejects the generated UPDATE statement because the target table (location alias l1_0) is also referenced inside the subquery in the FROM clause, producing the error:
You can't specify target table 'l1_0' for update in FROM clause.

This prevents the deletion process from completing.

To Reproduce
Steps to reproduce the behavior:

Send a DELETE request to the StudyMgtRS endpoint to delete a study.

The study must contain locations associated with instances.

MySQL attempts to execute the Hibernate-generated SQL statement.

The error is thrown and the study is not deleted.

Expected behavior
The study should be successfully marked for deletion, and the location rows should be updated without violating MySQL’s restriction against updating a table that is also used in the subquery.

jakarta.ejb.EJBException: org.hibernate.exception.GenericJDBCException: JDBC exception executing SQL [update location l1_0 set status=?,instance_fk=null where l1_0.pk in (select l2_0.pk from location l2_0 join instance i2_0 on i2_0.pk=l2_0.instance_fk join series s1_0 on s1_0.pk=i2_0.series_fk where s1_0.study_fk=? and l2_0.status<>? and l2_0.object_type=? and l2_0.multi_ref is null and l2_0.uidmap_fk is null)]
[You can't specify target table 'l1_0' for update in FROM clause]