Modifies a row in a table or view.
1 – Environment
You can use the UPDATE statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
UPDATE -+-> <table-name> -+-+---------------------+-+
+-> <view-name> --+ +> <correlation-name> + |
+-------------------------------------------------+
+-> SET -+> <column-name> = -+-> value-expr -+-+-+
| +-> NULL -------+ | |
| +-> DEFAULT ----+ | |
+--------------- , <------------------+ |
+------------------------- <---------------------+
++--------------------------------------------------------+-+
+-> WHERE -+-> predicate -+---------------------------+-++ |
| +> optimize-clause ---------+ | |
+-> CURRENT OF <cursor-name> ----------------+ |
+------------------------- <--------------------------------+
++----------------------+------------------------------------>
+-> returning-clause --+
optimize-clause =
--+---------------------------------------------------------------+--->
+-> OPTIMIZE --+-+-> FOR -+-> FAST FIRST --------+----------+-+-+
| | +-> TOTAL TIME --------+ | |
| | +-> SEQUENTIAL ACCESS -+ | |
| +-> USING <outline-name> ------------------+ |
| +-> WITH -+-> DEFAULT --+-> SELECTIVITY -+ |
| | +-> SAMPLED --+ | |
| | +-> AGGRESSIVE + | |
| +-> AS <query-name> -----------------------+ |
+---------------- <----------------------------+
returning-clause=
--+------------------------------+-+-------------------------+->
+-> RETURNING +-> value-expr +-+ +-> INTO +> <parameter> +-+
+------ , <----+ +------ , <----+
3 – Arguments
3.1 – column-name
Specifies the name of a column whose value you want to modify.
3.2 – correlation-name
Specifies a name you can use to identify the table or view in the
predicate of the UPDATE statement. See the User_Supplied_Names
HELP topic for more information about correlation names.
3.3 – CURRENT OF cursor name
If the WHERE clause uses CURRENT OF cursor-name, SQL modifies
only the row on which the named cursor is positioned. The cursor
named in an UPDATE statement must meet these conditions:
o The cursor must have been named previously in a DECLARE CURSOR
statement or FOR statement.
o The cursor must be open.
o The cursor must be on a row.
o The FROM clause of the SELECT statement within the DECLARE
CURSOR statement must refer to the table or view that is the
target of the UPDATE statement.
3.4 – DEFAULT
SQL assigns the DEFAULT defined for the column or domain. If no
DEFAULT is defined, then NULL is assumed.
If the DEFAULT clause is used in an UPDATE statement then one of
the following will be applied:
o If a DEFAULT attribute is present for the column then that
value will be applied during UPDATE.
o Else if an AUTOMATIC attribute is present for the column then
that value will be applied during UPDATE. This can only happen
if the SET FLAGS 'AUTO_OVERRIDE' is used since during normal
processing these columns are read-only.
o Otherwise a NULL will be applied during UPDATE.
3.5 – INTO parameter
Inserts the value specified to a specified parameter.
The INTO parameter clause is optional in interactive SQL. In this
case the returned values are displayed.
3.6 – NULL
Specifies a NULL keyword. SQL assigns a null value to columns for
which you specify NULL. Any column assigned a null value must be
defined to allow null values (defined in a CREATE or ALTER TABLE
statement without the NOT NULL clause).
3.7 – OPTIMIZE_AS
Assigns a name to the query.
3.8 – OPTIMIZE_FOR
The OPTIMIZE FOR clause specifies the preferred optimizer
strategy for statements that specify a select expression. The
following options are available:
o FAST FIRST
A query optimized for FAST FIRST returns data to the user as
quickly as possible, even at the expense of total throughput.
If a query can be cancelled prematurely, you should specify
FAST FIRST optimization. A good candidate for FAST FIRST
optimization is an interactive application that displays
groups of records to the user, where the user has the option
of aborting the query after the first few screens. For
example, singleton SELECT statements default to FAST FIRST
optimization.
If optimization strategy is not explicitly set, FAST FIRST is
the default.
o TOTAL TIME
If your application runs in batch, accesses all the records in
the query, and performs updates or writes a report, you should
specify TOTAL TIME optimization. Most queries benefit from
TOTAL TIME optimization.
o SEQUENTIAL ACCESS
Forces the use of sequential access. This is particularly
valuable for tables that use the strict partitioning
functionality.
3.9 – OPTIMIZE_USING
Explicitly names the query outline to be used with the UPDATE
statement even if the outline ID for the query and for the
outline are different.
3.10 – OPTIMIZE_WITH
Selects one of three optimzation controls: DEFAULT (as used by
previous versions of Rdb), AGGRESSIVE (assumes smaller numbers of
rows will be selected), and SAMPLED (which uses literals in the
query to perform preliminary estimation on indices).
3.11 – predicate
If the WHERE clause includes a predicate, all the rows of the
target table for which the predicate is true are modified.
The columns named in the predicate must be columns of the target
table or view. The target table cannot be named in a column
select expression within the predicate.
See the Predicates HELP topic for more information on predicates.
3.12 – RETURNING value expr
Returns the value of the column specified in the value
expression. If DBKEY is specified, SQL returns the database key
(dbkey) of the row being updated. When the DBKEY value is valid,
subsequent queries can use the DBKEY value to access the row
directly.
The RETURNING DBKEY clause is not valid in an UPDATE statement
used to assign values to the segments in a column of the LIST OF
BYTE VARYING data type.
Only one row can be updated when you specify the RETURNING
clause.
3.13 – SET
Specifies which columns in the table or view get what values. For
each column you want to modify, you must specify the column name
and either a value expression, the NULL keyword, or the DEFAULT
keyword. SQL assigns the value following the equal sign to the
column that precedes the equal sign.
3.14 – table-name
Specifies the name of the target table that you want to modify.
3.15 – value-expr
Specifies the new value for the modified column. Columns named in
the value expression must be columns of the table or view named
after the UPDATE keyword. The values can be specified through
parameters, qualified parameters, column select expressions,
value expressions, or the default values.
3.16 – view-name
Specifies the name of the target view that you want to modify.
3.17 – WHERE
Specifies the rows of the target table or view that will be
modified according to the values indicated in the SET clause.
If you omit the WHERE clause, SQL modifies all rows of the target
table or view. You can specify either a predicate or a cursor
name in the WHERE clause.
4 – Examples
Example 1: Using the UPDATE statement in interactive SQL
The following interactive SQL example changes the address of the
employee with EMPLOYEE_ID 00164 and confirms the change:
SQL> UPDATE EMPLOYEES
cont> SET ADDRESS_DATA_1 = '16 Ridge St.'
cont> WHERE EMPLOYEE_ID = '00164';
1 row updated
SQL> SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, ADDRESS_DATA_1
cont> FROM EMPLOYEES
cont> WHERE EMPLOYEE_ID = '00164';
EMPLOYEE_ID FIRST_NAME LAST_NAME ADDRESS_DATA_1
00164 Alvin Toliver 16 Ridge St.
1 row selected
Example 2: Using the UPDATE statement in a program
The following example illustrates using a host language variable
in an embedded SQL statement to update an employee's status code:
DISPLAY "Enter employee's ID number: " WITH NO ADVANCING.
ACCEPT ID.
DISPLAY "Enter new status code: " WITH NO ADVANCING.
ACCEPT STATUS-CODE.
EXEC SQL
DECLARE TRANSACTION READ WRITE
END-EXEC
EXEC SQL
UPDATE EMPLOYEES
SET STATUS_CODE = :STATUS-CODE
WHERE EMPLOYEE_ID = :ID
END-EXEC
EXEC SQL COMMIT END-EXEC