Replaces the contents of fields with data from expressions.

Syntax

REPLACE
<field 1> WITH <exp 1> [ADDITIVE]
[, <field 2> WITH <exp 2> [ADDITIVE]...]
[<scope>]
[FOR <condition 1>]
[WHILE <condition 2>]
[REINDEX]

<field> WITH <exp>

Designates fields to be replaced by the value of the specified expressions. Multiple fields of a record may be changed by including additional <field n> WITH <exp n> expressions, separated by commas.

ADDITIVE

Adds text to the end of memo field text instead of replacing existing text. You can use ADDITIVE only when the specified field is a memo field in a DBF table.

<scope>
FOR <condition 1>
WHILE <condition 2>

The scope of the command. The default scope is NEXT 1, the current record only.

REINDEX

Specifies that all affected indexes are rebuilt once the REPLACE operation finishes. Without REINDEX, dBASE Plus updates all open indexes after replacing each record in the scope. When replacing many or all records in a table that has multiple open indexes, REPLACE executes faster with the REINDEX option.

Description

The REPLACE command overwrites a specified field with new data. The field you select can be any type, including memo fields. (To replace binary or OLE fields, use REPLACE BINARY and REPLACE OLE.) The field and the expression specified by the WITH clause must have the same data type.

When storing a long string or the contents of a memo field into a shorter character field, the data is truncated. Use the ADDITIVE option to add a character string to the end of existing memo field text. You can leave a blank space at the beginning of the string to provide proper spacing.

Be careful when replacing data in the key fields of the current master index, in more than one record (that is, with the <scope>, WHILE, or FOR options).dBASE Plus automatically updates all open index files after a REPLACE operation finishes. After replacing data that changes the value in the key field in the master index, the record and the record pointer immediately move to the position in the index based on the new value of a key. If replacement in the key field causes a record (and the pointer) to move down past other records that fall within the scope or meet the specified conditions, those records are not replaced. If replacement in the key field causes a record to move up before records that have already been replaced, those records may be replaced again.

To make replacements to an indexed table's key field, you may place the table in natural order with the SET ORDER TO command, or use other techniques, one of which is shown in the example. Replacements in key fields other than the key fields of the master index don't affect the order of the current index and can be made over multiple records without complications.

When replacing a Numeric or Float field in a DBF table, the magnitude of the new value should not exceed the integer portion of the field. For example, if the Numeric field is defined as width 4 with 1 decimal place, you cannot have a number greater than 99.9. If so, the field contents are replaced with an approximation to the new value in scientific notation, if it will fit; otherwise the field contents are replaced with asterisks, destroying stored data. Scientific notation requires a field width of at least 7 characters. This condition is not an error, but dBASE Plus will display a numeric overflow warning message in the result pane of the Command window.

Other field types that store numbers, including Long and Short integers, have a numeric range they support. Make sure that the number you attempt to store does not exceed those ranges.

Use the alias operator in the <field> (that is, alias–>field) to REPLACE fields in tables other than the currently selected table. You may mix fields from different tables in the same REPLACE statement, although the scope of the command is based on the current table. If there is no relation between the current table and other tables, traversing the current table—for example, because of an ALL scope—does not move the record pointer in the other tables.

OODML

Assign values directly to the value properties of the Field objects in the Rowset object’s fields array (in a loop that traverses the rowset if necessary).