INSERT, UPDATE and DELETE operations may be performed through
views or as qualified by search conditions provided the views or search
conditions are based on updatable queries. UPDATE WHERE CURRENT and
DELETE WHERE CURRENT operations may be performed through cursors
provided the cursors are based on updatable queries.
Queries that underlie views and cursors are called updatable queries
when they conform to all of the following updatability criteria:
No DISTINCT, GROUP BY, or HAVING clause is specified in the
outermost SELECT statement; and no aggregate is specified in
the outermost select list.
The FROM clause specifies exactly one table, either directly or
through a view. If the FROM clause specifies a view, the view must be
based on an updatable query.
For INSERT and UPDATE through views, the select list
in the view definition must not contain any arithmetic expressions.
It must contain only column names.
For UPDATE WHERE CURRENT and DELETE WHERE CURRENT
operating on cursors, the cursor declaration must not include an
ORDER BY clause, and the query expression must not contain
subqueries, the UNION or UNION ALL statement, or any nonupdatable
views.
The target table of an INSERT, UPDATE, or DELETE
operation is the base table to which the changes are actually being
made.
For noncursor INSERT, UPDATE, or DELETE operations,
the view definition must not include any subqueries which contain the
target table in their FROM clause; and if a search condition is
given, it must not include any subqueries which contain the target
table in their FROM clause.
If a query is updatable by the previous rules, then the underlying
table is an updatable table. Otherwise it
is considered a read-only table and is locked
accordingly. This means that in cursor operations, SIX, IX, and
X locks are not used unless the query that underlies the cursor
matches the updatability criteria and was declared with columns
for UPDATE. In noncursor view operations, SIX, IX, and X locks are
not obtained unless the table underlying the view is updatable.
Refer to Chapter 5 "Concurrency Control
through Locks and Isolation Levels" for a complete explanation
of SIX, IX, and X locks.