2.10.2.10. JPQL Bulk Update and Delete

Bulk update and delete operations apply to entities of a single entity class (together with its subclasses, if any). Only one entity abstract schema type may be specified in the FROM or UPDATE clause. The syntax of these operations is as follows:

  • update_statement ::= update_clause [where_clause]

  • update_clause ::= UPDATE entity_name [[AS] identification_variable] SET update_item {, update_item}*

  • update_item ::= [identification_variable.]{state_field | single_valued_object_field} = new_value

  • new_value ::= scalar_expression | simple_entity_expression | NULL

  • delete_statement ::= delete_clause [where_clause]

  • delete_clause ::= DELETE FROM entity_name [[AS] identification_variable]

The syntax of the WHERE clause is described in JPQL WHERE Clause.

A delete operation only applies to entities of the specified class and its subclasses. It does not cascade to related entities.

The new_value specified for an update operation must be compatible in type with the state-field to which it is assigned.

Bulk update maps directly to a database update operation, bypassing optimistic locking checks. Portable applications must manually update the value of the version column, if desired, and/or manually validate the value of the version column.

The persistence context is not synchronized with the result of the bulk update or delete.

Caution should be used when executing bulk update or delete operations because they may result in inconsistencies between the database and the entities in the active persistence context. In general, bulk update and delete operations should only be performed within a transaction in a new persistence context or at the beginning of a transaction (before entities have been accessed whose state might be affected by such operations).

Examples:

DELETE FROM Publisher pub WHERE pub.revenue  1000000.0
DELETE FROM Publisher pub WHERE pub.revenue = 0 AND pub.magazines IS EMPTY
UPDATE Publisher pub SET pub.status = 'outstanding'
    WHERE pub.revenue  1000000 AND 20  (SELECT COUNT(mag) FROM pub.magazines mag)