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
1 – Format
(B)0[m[4mMODIFY[m qqq> context-var qqqk
lqqqqqqqqqq<qqqqqqqqqj
mqq> [4mUSING[m 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> [4mEND_MODIFY[m qqqq>
1.1 – on-error
The ON ERROR clause, which specifies a host language statement to
be performed if an Oracle Rdb error occurs.
1.2 – 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.
1.3 – record-descr
A valid data dictionary record descriptor matching all the fields
of the relation.
1.4 – field-name
The name of the field to be modified.
1.5 – 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.
3 – 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