Not all valid view definitions are updatable. In other words, there are several types of view definitions that do not permit you to perform DELETE, INSERT, or UPDATE operations against them. The general rule for updatability of a view is that there must be a 1:1 correspondence between a row defined in a view and the same row defined in an underlying base table on which that view is defined. If that 1:1 correspondence does not exist, then the view is not updatable.
The following features, when specified as a component of a view definition, automatically make that view non-updatable, or read-only.
- Expressions
- Joins
If a view definition specifies more than one table, it is not updatable.
- Any form of derived column
- Any form of aggregation, including the following specific types:
- Ordered analytical functions
- Aggregate functions
- Any form of aggregation-related clauses, including the following:
- GROUP BY clause
- HAVING clause
- QUALIFY clause
- Set operators, including all forms of the following:
- EXCEPT and MINUS
- INTERSECT
- UNION
- A TOP n or TOP m PERCENT clause
- The DISTINCT operator
- A WHERE clause that specifies a nested table expression that references the same table as is referenced by the main WHERE clause for the view definition
You also cannot execute any of the following HELP statements against a non-updatable view.
- HELP CONSTRAINT
- HELP INDEX
- HELP STATISTICS