Скачать или смотреть Resolving the Column 'is_deleted' is ambiguous Error in JPQL Update Queries with Hibernate

Learn how to fix the common 'is_deleted' column ambiguity error in Hibernate JPQL update queries by using subqueries instead of join-based updates.
---
This video is based on the question https://stackoverflow.com/q/79474861/ asked by the user 'Jeswin George' ( https://stackoverflow.com/u/27263413/ ) and on the answer https://stackoverflow.com/a/79475606/ provided by the user 'Oleg Cheban' ( https://stackoverflow.com/u/29227008/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to resolve "Column 'is_deleted' in field list is ambiguous" error in JPQL update query when using Hibernate?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to drop me a comment under this video.
---
Understanding the Problem

When updating entities using JPQL in Spring Data JPA with Hibernate, you might encounter the error:

[[See Video to Reveal this Text or Code Snippet]]

This typically happens if the same column name, like is_deleted, exists in multiple joined tables. Hibernate’s generated SQL does not qualify the column with a table alias in the SET clause, leading to ambiguity.

Example Scenario

A query like:

[[See Video to Reveal this Text or Code Snippet]]

generates SQL similar to:

[[See Video to Reveal this Text or Code Snippet]]

Here, the is_deleted column in the SET clause is not qualified with a table alias, causing ambiguity.

Why Does This Happen?

JPQL Restrictions: JPQL does not support JOIN clauses in an UPDATE query.

Database Limitations: Databases like Oracle and PostgreSQL don't support table aliases in the SET clause.

Hibernate Behavior: Hibernate translates the JPQL but cannot apply aliases in SET, resulting in ambiguous SQL.

Recommended Solution: Use a Subquery

Since direct joins in JPQL update are problematic, rewrite the query using a subquery in the WHERE clause instead of navigating through relationships:

[[See Video to Reveal this Text or Code Snippet]]

Why It Works

Avoids join-based updates that databases and JPQL don't support.

The subquery cleanly filters ActivityEnvironmentRelation rows by related Activity ids.

Hibernate generates unambiguous SQL without alias conflicts.

Additional Tips

Always check if your JPQL update queries involve joins; they generally are unsupported.

If more complex updates are needed, consider:

Splitting into multiple queries

Using native SQL queries, as a last resort

Applying bulk update frameworks or Criteria API if applicable

Summary

To fix the "Column 'is_deleted' in field list is ambiguous" error in Hibernate JPQL updates:

Avoid using relationships with joins in the UPDATE query.

Replace joins with subqueries in the WHERE condition.

This approach is portable across databases like Oracle and PostgreSQL.

This method keeps your JPA queries clean, efficient, and free from ambiguous column errors.