UPDATE WHERE CURRENT

This statement cannot be used interactively and should not be used in conjunction with rows fetched using the BULK FETCH statement.

For constraint violations, the error handling behavior depends on the setting of the SET CONSTRAINTS statement. Refer to the discussion of this statement 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 WHERE CURRENT OF CURSOR requires that the cursor be based on an updatable query. See "Updatability of Queries" in the "SQL Queries" chapter.

The target table of the UPDATE WHERE CURRENT is designated by TableName or is the base table underlying the ViewName. The base table restrictions that govern updates via a cursor were presented in the description of the DECLARE CURSOR statement.

A table on which a unique constraint is defined cannot contain duplicate rows.

For constraint violations, the error handling behavior depends on the setting of the SET CONSTRAINTS statement. Refer to the discussion of this statement in this chapter.

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.

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 still be visible through the query expression of the check constraint 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 for a further discussion on check constraints.

A rule defined with a StatementType of UPDATE will affect UPDATE WHERE CURRENT 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 WHERE CURRENT statements performed on the rules' target tables that include at least one of the columns in their SET clause. When the UPDATE WHERE CURRENT 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 the current row and invoke its associated procedure with the specified parameters. If the rule has a condition, it will evaluate the condition and fire if the condition evaluates to TRUE, invoking the associated procedure with the specified parameters for the current 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 in effect, the UPDATE WHERE CURRENT 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 WHERE CURRENT 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.

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. 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.