|
|
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.
ISQL or Application Programs
UPDATE {[Owner.]TableName
[Owner.]ViewName)
SET {ColumnName = {Expression
'LongColumnIOString'
NULL } } [,...]
[WHERE SearchCondition ]
- [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.
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 Chapter 3
"SQL Queries"
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.
{ [<{[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-2 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, 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.
UPDATE PurchDB.Parts SET SalesPrice = SalesPrice * 1.25
WHERE SalesPrice > 500.00
|