replace

Syntax diagram for the replace statement

record name: Name of the I/O object: an indexed, relative, or SQL record.
with #sql{ sqlStatement }: An explicit SQL UPDATE statement. Leave no space between #sql and the left brace.
fromresultSetID: ID that ties the replace statement to a get or open statement run earlier in the same program. For details, see resultSetID.

The following example shows how to read and replace a file record:

  emp.empnum = 1;         // sets the key in record emp

  try
    get emp forUpdate;
  onException
    myErrorHandler(8);  // exits the program
  end

  emp.empname = emp.empname + " Smith";

  try
    replace emp;
  onException
    myErrorHandler(12);
  end

Details on the replace statement depend on the record type. For details on SQL processing, see SQL record.

Indexed or relative record

If you want to replace an indexed or relative record, issue a get statement for the record with the forUpdate option, then issue the replace statement with no intervening I/O operation against the same file. After you invoke the replace statement, the effect of the next I/O operation on the same file is as follows:

If the next I/O operation is a replace statement on the same EGL record, the record is changed in the file
If the next I/O operation is a delete statement on the same EGL record, the record in the file is marked for deletion
If the next I/O operation is a get statement on a record in the same file and includes the forUpdate option, a subsequent replace or delete statement is valid on the newly read file record
If the next I/O operation is a get statement on the same EGL record (with no forUpdate option) or is a close statement on the same file, the file record is released without change

For details on the forUpdate option, see get.

SQL record

In the case of SQL processing, the EGL replace statement results in an SQL UPDATE statement in the generated code.

You must retrieve a row for subsequent replacement, in either of two ways:

Issue a get statement (with the forUpdate option) to retrieve the row; or
Issue an open statement to select a set of rows, then invoke a get next statement to retrieve the row of interest.

Error conditions

The following conditions are among those that are not valid when you use a replace statement:

You specify an SQL statement of a type other than UPDATE
You specify some but not all clauses of an SQL UPDATE statement
You do not specify a resultSetID value when one is necessary; for details, see resultSetID
You specify (or accept) an UPDATE statement that has one of these characteristics--
- Updates multiple tables
- Is associated with a column that either does not exist or is incompatible with the related host variable
You use an SQL record as an I/O object, and all the record items are read only

The following situation also causes an error:

You customize an EGL get statement with the forUpdate option, but fail to indicate that a particular SQL table column is available for update; and
The replace statement that is related to the get statement tries to revise the column.

You can solve the previous mismatch in any of these ways:

When you customize the EGL get statement, include the column name in the SQL SELECT statement, FOR UPDATE OF clause; or
When you customize the EGL replace statement, eliminate reference to the column in the SQL UPDATE statement, SET clause; or
Accept the defaults for both the get and replace statements.

Implicit SQL statement

By default, the effect of a replace statement that writes an SQL record is as follows:

As a result of the association of record items and SQL table columns in the record declaration, the generated code copies the data from each record item into the related SQL table column
If you defined a record item to be read only, the value in the column that corresponds to that record item is unaffected

The SQL statement has these characteristics by default:

The SQL UPDATE statement does not include record items that are read only

The SQL UPDATE statement for a particular record is similar to the following statement:

    UPDATE tableName
    SET column01 = :recordItem01,
        column02 = :recordItem02,
                 .
                 .
                 .
        columnNN = :recordItemNN    WHERE CURRENT OF cursor

Related tasks
Syntax diagram