|
|
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.
Application Programs Only
UPDATE {[Owner.]TableName
[Owner.]ViewName}
SET { ColumnName = {Expression
'LongColumnIOString'
NULL }}[,...]
WHERE CURRENT OF CursorName
- [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.
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.
{ [<{[PathName/]FileName
%SharedMemoryAddress}]
[{>
>>
>!}[PathName/]{FileName
CharString$
CharString$CharString}
>% {SharedMemoryAddress
$ } ] } [...]
- < [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.
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.
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.
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
|