ALLBASE/SQL Reference Manual (36216-90216)

136 Chapter3

SQL Queries

Updatability of Queries

INSERT, UPDATE and DELETE operations may be performed through views or as qualiﬁed

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 speciﬁed in the outermost SELECT

statement; and no aggregate is speciﬁed in the outermost select list.

• The FROM clause speciﬁes exactly one table, either directly or through a view. If the

FROM clause speciﬁes a view, the view must be based on an updatable query.

•ForINSERT and UPDATE through views, the select list in the view deﬁnition must not

contain any arithmetic expressions. It must contain only column names.

•ForUPDATE 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,orDELETE operation is the base table to which

the changes are actually being made.

• For noncursor INSERT, UPDATE, or DELETE operations, the view deﬁnition 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.