ALLBASE/SQL Reference Manual (36216-90216)
Chapter 12 581
SQL Statements S - Z
UPDATE
the table before updating any row. Each subquery in the search condition is effectively
executed for each row of the table, and the results used in the application of the search
condition to the given row. If any executed subquery contains an outer reference to a
column of the table, the reference is to the value of that column in the given row.
• If ALLBASE/SQL detects an error during a multiple-row UPDATE operation, the error
handling behavior depends on the setting of the SET DML ATOMICITY and the SET
CONSTRAINTS statements. Refer to the discussion of these statements in this chapter.
• No error or warning condition is generated by ALLBASE/SQL when a character or
binary string is truncated during an UPDATE operation.
• Using UPDATE with views requires that the views be updatable. See "Updatability of
Queries" in Chapter 3 , “SQL Queries.”
• The target table of the UPDATE is designated by
TableName
or is the base table of
ViewName
. This target table must be updatable and must not be identified in a FROM
clause of any subquery contained in the
SearchCondition
.
• A table on which a unique constraint is defined cannot contain duplicate rows.
• An update of a primary key column in either a referential or unique constraint will fail
if any of the rows being updated are currently referred to by any table's foreign key row
or if any of the rows being updated ends up matching the value of another unique row.
In order to update such primary key rows, the foreign keys must be changed to refer to
other primary keys, changed to a value of NULL, or deleted. An update of a foreign key
column will fail if it leaves a non-NULL foreign key row without any matching primary
key row.
• Integrity constraints on tables or views are enforced on a statement level basis, when
SET DML ATOMICITY and SET CONSTRAINTS are at their default values. Thus it is
possible to update constraint keys using SET clauses like the following:
SET Column1 = Column1 + 1
even when the initial values of Column1 are a set of sequential integers, such as 1, 2, 3,
4 (which causes a temporary unique constraint violation). If at the end of the UPDATE
statement (that is, after all rows have been incremented), the unique constraint is
satisfied, no error message is generated.
• Rows being updated must not cause the search condition of the table check constraint to
be false and must cause the search condition of the view check constraint to be true
when error checking is done.
• Rows being updated in the table through a view having a WITH CHECK OPTION must
be visible through the query expression of the view and any underlying views, in
addition to satisfying any constraints of the table. Refer to the "Check Constraints"
section of the "Constraints, Procedures, and Rules" chapter.
• Rules defined with a
StatementType
of UPDATE will affect UPDATE statements
performed on the rules' target tables. Rules defined with a
StatementType
of
UPDATE including a list of column names will affect only those UPDATE statements
performed on the rules' target tables that include at least one of the columns in the
UPDATE's SET clause. When the UPDATE is performed, ALLBASE/SQL considers all
the rules defined for that table with the UPDATE
StatementType
and a matching