Chapter 4. Updatable Views

4.1. Updatable Views

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 handling 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 partitioned union and the INSERT specifies values that belong to a single partition. Refer to Partitioned Union.
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 key-preserved table.
  • To allow DELETE operations there must be only a single 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 update procedures (see Section 2.10.6, “Update Procedures”) to define procedures to handle the respective operations.