UPDATE

If the WHERE clause is omitted, all rows of the table are updated as specified by the SET clause.

If the WHERE clause is present, then the search condition is evaluated for each row of 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 the "SQL Queries" chapter.

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 column. If the rule has no condition, it will fire for all rows affected by the statement and invoke its associated procedure with the specified parameters on each row. If the rule has a condition, it will evaluate the condition on each row. The rule will fire on rows for which the condition evaluates to TRUE and invoke the associated procedure with the specified parameters for each row. Invoking the procedure could cause other rules, and thus other procedures, to be invoked if statements within the procedure trigger other rules.

If a DISABLE RULES statement is issued, the UPDATE statement will not fire any otherwise applicable rules. When a subsequent ENABLE RULES is issued, applicable rules will fire again, but only for subsequent UPDATE statements, not for those rows processed when rule firing was disabled.

In a rule defined with a StatementType of UPDATE, any column reference in the Condition or any ParameterValue that specifies the OldCorrelationName will refer to the value of the column before the SET clause assignment is performed on it. Any column reference that specifies the NewCorrelationName or TableName will refer to the value of the column after the SET clause assignment is performed on it.

The set of rows to be affected by the UPDATE statement is determined before any rule fires, and this set remains fixed until the completion of the rule. If the rule adds to, deletes from, or modifies this set, such changes are ignored.

When a rule is fired by this statement, the rule's procedure is invoked after the changes have been made to the database for that row and all previous rows. The rule's procedure, and any chained rules, will thus see the state of the database with the current partial execution of the statement.

If an error occurs during processing of any rule considered during execution of this statement (including execution of any procedure invoked due to a rule firing), the statement and any procedures invoked by any rules will have no effect. Nothing will have been altered in the DBEnvironment as a result of this statement or the rules it fired. Error messages are returned in the normal way.

The input device must not be locked or have privilege security.

An input device file can be a standard MPE/iX file with fixed record size, valid blocking factor, valid file equations, ASCII/binary option, and user labels option. Any related output device file will have the same characteristics as the input device file.

When the input device is a heap address and no output device is specified, output is a standard MPE/iX file with an 80-byte record size, a blocking factor of 1, a binary option, and a file size equal to the LONG column size.

When the input device is a file and no output device is specified, ALLBASE/SQL generates a random file name with the same characteristics as the input file specified in the LONG column I/O string. If the input file contains labels, then the output file contains the same amount of labels.

When you specify a portion of the output file name in conjunction with the wildcard character $, a five-byte, alphanumeric character string replaces the wildcard. The wildcard character can appear in any position of the output device name except the first. The maximum file name being 8 bytes, you can specify 3 bytes of the device name, at least one of which is in the first character position of the name and is not numeric.

When no portion of the output device name is specified, the default file name, tmp$, is used. The wildcard character ($) indicates a random, five-byte, alphanumeric character string. This file is created in the local group.

The wildcard character, whether user specified or part of the default output device name, is an almost unique five-byte, alphanumeric character string. The possibility of two identical wildcards being generated is remote.

When a file is used as the LONG column input or output device and you do not specify a group and account name in the LONG column I/O string, the default is the group and account in which your program is running.

The output device cannot be overwritten with a SELECT or FETCH statement unless you use the INSERT or UPDATE statement with the overwrite option.

If no input device is specified, only output information of LONG columns is reset.

If no output device is specified, only value is reset.

If you own the view, you need UPDATE or OWNER authority for the base table, or UPDATE authority for each column of the base table to be updated as specified in the SET clause, or DBA authority.

If you do not own the view, you must have UPDATE authority for the view, or UPDATE authority for each column of the view specified in the SET clause, or DBA authority. In addition, the owner of the view must have UPDATE or OWNER authority with respect to the view's definition, or the owner must have DBA authority.

Using UPDATE with views requires that the views be updatable. See "Updatability of Queries" in the "SQL Queries" chapter.