HP 3000 Manuals HP 3000 Manuals
Updatability of Queries
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 the
chapter "Concurrency Control through Locks and Isolation Levels" for a
complete explanation of SIX, IX, and X locks.