Search code examples
javaspring-boothibernatejpql

How to resolve "Column 'is_deleted' in field list is ambiguous" error in JPQL update query when using Hibernate?

I'm trying to update a table using a JPQL query in Spring Data JPA, but I am encountering an error: "Column 'is_deleted' in field list is ambiguous." The error occurs because the same column name, is_deleted, is present in both the ActivityEnvironmentRelation and Activity entities.

Below is my JPQL query:

@Modifying
@Query("UPDATE ActivityEnvironmentRelation aer "
       + "SET aer.isDeleted = 1, aer.updatedBy = :updatedBy "
       + "WHERE aer.activity.project.objectId = :projectId")
void deleteActivityEnvironments(@Param("projectId") Long projectId, @Param("updatedBy") User updatedBy);

Hibernate generates the following SQL query:

UPDATE activity_environment aer1_0 
JOIN activity a1_0 
ON a1_0.object_id = aer1_0.activity_id 
SET is_deleted = 1, updated_by = ? 
WHERE a1_0.project_id = ?

As you can see, the SQL query does not qualify the is_deleted column with a table alias, causing the ambiguity.

It seems that Hibernate does not add table aliases to the SET clause, which leads to this error.

My question is: Is this behavior the default nature of Hibernate, where it doesn't automatically qualify column names in complex UPDATE queries? And is there any solution to resolve this issue without resorting to a native SQL query?

Solution

  • I have been working with Oracle and PostgreSQL, and I can confirm that neither of them supports table aliases in the SET clause, nor do they allow direct UPDATE with JOIN. Which database are you using?

    I doubt that JPQL allows using relationships (aer.activity.project.objectId) in an UPDATE statement. I recommend rewriting your UPDATE statement using a subquery, like this:

    @Modifying
@Query("UPDATE ActivityEnvironmentRelation aer "
       + "SET aer.isDeleted = 1, aer.updatedBy = :updatedBy "
       + "WHERE aer.activity.id IN ("
       + "  SELECT a.id FROM Activity a WHERE a.project.objectId = :projectId"
       + ")")
void deleteActivityEnvironments(@Param("projectId") Long projectId, @Param("updatedBy") User updatedBy);