HP 3000 Manuals HP 3000 Manuals
UPDATE
The UPDATE statement updates the values of one or more columns in all
rows of a table or in rows that satisfy a search condition.
Scope
ISQL or Application Programs
SQL Syntax
{ {Expression }}
UPDATE {[Owner.]TableName} SET {ColumnName = {'LongColumnIOString'}}
{[Owner.]ViewName } { {NULL }}
[,...] [WHERE SearchCondition]
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 statement.
Expression is any expression that does not contain an
aggregate function or a LONG column (except via
the long column function). The expression is
evaluated for each row qualifying for the update
operation. 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 of each
row satisfying the WHERE clause. The column must
allow null values.
SearchCondition specifies a search condition; the search
condition cannot contain an aggregate function.
All rows for which the search condition is true
are updated as specified in the SET clause. Rows
that do not satisfy the search condition are not
affected. If no rows satisfy the search
condition, the table is not changed.
Description
* 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.
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, the authority needed as described below 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.
* Using UPDATE with views requires that the views be updatable. See
"Updatability of Queries" in the "SQL Queries" chapter.
Example
UPDATE PurchDB.Parts SET SalesPrice = SalesPrice * 1.25 WHERE SalesPrice > 500.00