1  MODIFY
   Changes the value in a field in one or more records from a
   relation or open stream. Before you use the MODIFY statement,
   you must start a read/write transaction and establish a record
   stream with a FOR statement or a START_STREAM statement. The
   context variables you refer to in MODIFY statement must be the
   same as those defined in the FOR or START_STREAM statement.

   Example:

   RDO>  FOR E IN EMPLOYEES WITH E.EMPLOYEE_ID = "00175"
   cont>  MODIFY E USING
   cont>     E.LAST_NAME = "Smathers"
   cont>  END_MODIFY
   cont> END_FOR
 

2  Format

 (B)0MODIFY qqq> context-var qqqk
       lqqqqqqqqqq<qqqqqqqqqj
       mqq> USING qqqqwqqqqqqqq>qqqqqqwqqqqqk
                      mqq> on-error qqj     x
       lqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqj
       tqqwq> context-var . field-name qq> = qqq> value-expr qqwqk
       x  mqqqqqqqqqqqqqqqqqqqq  ;  <qqqqqqqqqqqqqqqqqqqqqqqqqqj x
       mqqqq> context-var . * qqqqqqqqq> = qqq> record-descr qqqqu
       lqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqj
       mqqqq> END_MODIFY qqqq>

 

3  on-error
   The ON ERROR clause, which specifies a host language statement to
   be performed if an Oracle Rdb error occurs.
 

3  context-var
   A context variable declared in the FOR or START_STREAM statement.
   The MODIFY statement must appear after FOR or START_STREAM and
   before END_FOR or END_STREAM.

   You can modify fields in only one relation at a time. That is,
   all the context variables on the left side of the assignment in a
   MODIFY statement must refer to the same relation.
 

3  record-descr
   A valid data dictionary record descriptor matching all the fields
   of the relation.
 

3  field-name
   The name of the field to be modified.
 

3  value-expr
   an Oracle Rdb value expression that specifies the new value for the
   modified field.
 

2  More
   You need the Oracle Rdb READ and MODIFY privileges to the relation
   and also the Oracle Rdb MODIFY privilege to the database to use the
   MODIFY statement.

   Prior to Version 4.1, Oracle Rdb allowed you to modify rows in a
   table that was directly joined with other tables. Beginning
   with Version 4.1, Oracle Rdb returns an error message if you try
   to modify a row under these conditions. For example, Oracle Rdb will
   return the error, $RDMS-E-JOIN_CTX_UPD, relation EMPLOYEES is
   part of a join, cannot be updated, when you try to execute the
   following query:

   FOR S IN SALARY_HISTORY CROSS D IN DEGREES CROSS DP IN DEPARTMENTS
       WITH S.EMPLOYEE_ID = D.EMPLOYEE_ID AND
            S.EMPLOYEE_ID = DP.MANAGER_ID AND
            S.SALARY_END MISSING
            D.DEGREE = 'MA'
       MODIFY S USING S.SALARY_AMOUNT = S.SALARY_AMOUNT * 1.1
   END_FOR

   The preceding query will modify some salary history rows more
   than once and gives multiple salary raises to some managers. This
   query can be reworded using a subquery as follows:

   FOR S IN SALARY_HISTORY
       WITH S.SALARY_END MISSING AND
            (ANY D IN DEGREES CROSS DP IN DEPARTMENTS
             WITH S.EMPLOYEE_ID = D.EMPLOYEE_ID AND
             D.DEGREE = "MA")
       MODIFY S USING S.SALARY_AMOUNT - S.SALARY_AMOUNT * 1.1
   END_FOR

   This revised query will work with the new as well as the old
   update rules and it will ensure that each qualified manager gets
   a single salary raise.

   Note that some examples in the Guide to Using RDO, RDBPRE, and RDML
   will not work with the update rules introduced in Oracle Rdb
   Version 4.1. To run these examples, rewrite them using the ANY
   subquery mentioned previously.
 

2  Examples
   Example 1

   Modify a field value in a record:

      DISPLAY "Enter employee's ID number: " WITH NO ADVANCING.
      ACCEPT ID.
      DISPLAY "Enter new status code: " WITH NO ADVANCING.
      ACCEPT STATUS_CODE.

   &RDB&  START_TRANSACTION READ_WRITE

   &RDB&  FOR E IN EMPLOYEES WITH E.EMPLOYEE_ID = ID
   &RDB&    MODIFY E USING
   &RDB&        ON ERROR
                  GO TO ERROR-PAR
   &RDB&        END_ERROR
   &RDB&      E.STATUS_CODE = STATUS_CODE
   &RDB&    END_MODIFY
   &RDB&  END_FOR

   &RDB&  COMMIT