1  UPDATE
   Modifies a row in a table or view.
 

2  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> +-+
                 +------ , <----+            +------ , <----+
 

 

2  Arguments
 

3  column-name

   Specifies the name of a column whose value you want to modify.
 

3  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  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  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  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  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  OPTIMIZE_AS

   Assigns a name to the query.
 

3  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  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  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  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  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  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  table-name

   Specifies the name of the target table that you want to modify.
 

3  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  view-name

   Specifies the name of the target view that you want to modify.
 

3  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.
 

2  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