JBoss.orgCommunity Documentation
Any view may be marked as updatable. In many circumstances the view definition may allow the view to be inherently updatable without the need to manually define handing of INSERT/UPDATE/DELETE operations.
An inherently updatable view cannot be defined with a query that has:
A set operation (INTERSECT, EXCEPT, UNION).
SELECT DISTINCT
Aggregation (aggregate functions, GROUP BY, HAVING)
A LIMIT clause
A UNION ALL can define an inherently updatable view only if each of the UNION branches is itself inherently updatable. A view defined by a UNION ALL can support inherent INSERTs if it is a Section 14.2.8, “Partitioned Union” and the INSERT specifies values that belong to a single partition.
Any view column that is not mapped directly to a column is not updatable and cannot be targeted by an UPDATE set clause or be an INSERT column.
If a view is defined by a join query or has a WITH clause it may still be inherently updatable. However in these situations there are further restrictions and the resulting query plan may execute multiple statements. For a non-simple query to be updatable, it is required:
An INSERT/UPDATE can only modify a single Section 7.1, “Key-preserved Table”.
To allow DELETE operations there must be only a single Section 7.1, “Key-preserved Table”.
If the default handling is not available or you wish to have an alternative implementation of an INSERT/UPDATE/DELETE, then you may use Section 8.3, “Update Procedures” to define procedures to handle the respective operations.
A key-preserved table has a primary or unique key that would remain unique if it were projected into the result of the query. Note that it is not actually required for a view to reference the key columns in the SELECT clause. The query engine can detect a key preserved table by analyzing the join structure. The engine will ensure that a join of a key-preserved table must be against one of its foreign keys.