HP 3000 Manuals HP 3000 Manuals
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
{ {Expression }}
UPDATE {[Owner.]TableName} SET {ColumnName = {'LongColumnIOString'}}
{[Owner.]ViewName } { {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.[REV BEG] Refer to the
"Check Constraints" section of the "Constraints, Procedures, and
Rules" chapter for a further discussion on check constraints.[REV
END]
* 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
{[< {FileName[.Group[.Account]]}] }
{[ {%HeapAddress:LengthofHeap }] }
{ }
{[{> } {FileName[.Group[.Account]]}]} |...|
{[{>>} {CharString$ }]}
{[{>!} {CharString$CharString }]}
{[ ]}
{[>%$ ]}
Parameters--LongColumnIOString
FileName [GroupName [AccountName] ] is the location of the input file.
% HeapAddressValue:LengthOfHeap is the heap address where the input
is located and is of the specified
length.
> 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 will be
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.
>%$ is a heap address, generated by
ALLBASE/SQL, where the output is
placed. This option cannot be used
with a file name.
$ is the wild card character that
represents a random 5 byte
alphanumeric character string in
file names.
Description--LongColumnIOString
* 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.
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