UPDATE WHERE CURRENT

The UPDATE WHERE CURRENT statement updates the values of one or more columns in the current row associated with a cursor. The current row is the row pointed to by a cursor after the FETCH or REFETCH statement is issued.

Scope

Application Programs Only

SQL Syntax

  UPDATE {[Owner.]TableName 
          [Owner.]ViewName} 
  SET { ColumnName = {Expression 
                      'LongColumnIOString' 
                      NULL                 }}[,...] 
  WHERE CURRENT OF CursorName

Parameters

[Owner.]TableName: specifies the table to be updated.
[Owner.]ViewName: specifies a view; the table on which the view is based is updated. Refer to the CREATE VIEW statement for restrictions governing updates via views.
ColumnName: designates a column to be updated. You can update several columns of the same table with a single UPDATE WHERE CURRENT statement.
Expression: is any expression that does not contain an aggregate function or a LONG column (except via a long column function). The data type of the expression must be compatible with the column's data type.
'LongColumnIOString': specifies the input and output locations for the LONG data. The syntax for this string is presented in a separate section below.
NULL: puts a null value in the specified column. The column must allow null values.
CursorName: designates an opened cursor. The current row of the cursor is updated as specified by the SET clause. The column(s) named in the SET clause must also be named in the FOR UPDATE clause of the DECLARE CURSOR statement defining the cursor. After the update, the row updated remains the current row.

Description

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 Chapter 4 “Constraints, Procedures, and Rules” 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.

SQL Syntax — LongColumnIOString

  { [<{[PathName/]FileName
       %SharedMemoryAddress}]
    [{>
      >>
      >!}[PathName/]{FileName
                     CharString$
                     CharString$CharString}
     >% {SharedMemoryAddress
         $                                  } ] } |...|

Parameters — LongColumnIOString

< [PathName/] FileName: is the location of the input file.
<% SharedMemoryAddress: is the shared memory address where the input is located.
>: specifies that output is placed in the following file. If the file already exists, it is not overwritten nor appended to, and an error is generated.
>>: specifies that output is appended to the following file name. If the file does not exist, it is created.
>!: specifies that output is placed in the following file name. If the file already exists, it is overwritten.
>% SharedMemoryAddress: is the shared memory address where the output is placed.
>%$: is the shared memory address, determined by ALLBASE/SQL, where the output is placed.
$: is the wildcard character that represents a random, five-byte alphanumeric character string generated by ALLBASE/SQL. This is a file name.

Description — LongColumnIOString

The input device must have a permission allowing the login user to access it. For example, if the file belongs to the login user, permission must be at least 400. If the file belongs to another user, in a different group, permission must be at least 004.
When an output device has been specified and it exists prior to a SELECT or FETCH statement, ALLBASE/SQL does not change the file's owner or permission.

The output device, if it does not exist prior to a SELECT or FETCH statement, is created with the following characteristics.

Table 12-3 Default Output Device Characteristics

Device Type	Permission	UserID (uid)	GroupID (gid)
OUTPUT create	700	Current user login id	Current user login group
OUTPUT append	200	Current user login id	Current user login group
OUTPUT overwrite	200	Current user login id	Current user login group

If the output device exists prior to a SELECT or FETCH statement, in order for ALLBASE/SQL to access it for append or overwrite, the above characteristics are recommended.
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 14 bytes, you can specify 9 bytes of the device name.
When no portion of the output device name is specified, the default file name, tmp$.LF, is used. The wildcard character ($) indicates a random, five-byte, alphanumeric character string. This file is created in the local directory.
The wildcard character, whether user specified or part of the default output device name, is a unique five-byte, alphanumeric character string.
When a file is used as the LONG column input or output device and you do not give it a specific path name in the LONG column I/O string, the default is the path where ISQL or 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.
LONG columns cannot be used as follows:
- In a WHERE clause.
- In a type II INSERT statement.
- Remotely through ALLBASE/NET.
- As hash or B-tree index key columns.
- In a GROUP BY, ORDER BY, DISTINCT, or UNION clause.
- In an expression.
- In a subquery.
- In aggregate functions (AVG, SUM, MIN, MAX).
- As columns to which integrity constraints are assigned.
- With the DEFAULT option of the CREATE or ALTER TABLE statements.
If no input device is specified, only output information of LONG columns is reset.
If no output device is specified, only value is reset.

Authorization

You can update a table if you have UPDATE authority for the entire table, UPDATE authority for all of the columns specified in the SET clause, OWNER authority for the table, or DBA authority.

To update using a view, authority needed depends on whether you own the view:

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.

Example

A cursor for use in updating values in column QtyOnHand is declared and opened.

   DECLARE NewQtyCursor CURSOR FOR
    SELECT PartNumber,QtyOnHand FROM PurchDB.Inventory
           FOR UPDATE OF QtyOnHand
 
   OPEN NewQtyCursor

Statements setting up a FETCH-UPDATE loop appear next.

FETCH NewQtyCursor INTO :Num :Numnul, :Qty :Qtynul

Statements for displaying a row to and accepting a new QtyOnHand value from a user go here. The new value is stored in :NewQty.

             UPDATE PurchDB.Inventory\
                SET QtyOnHand = :NewQty\
   WHERE CURRENT OF NewQtyCursor
   .
   .
   . 
   CLOSE NewQtyCursor