Share via

REPLACE Command

  • Article

Updates table records.

REPLACE FieldName1 WITH eExpression1 [ADDITIVE]
   [, FieldName2 WITH eExpression2 [ADDITIVE]] ... [Scope]
   [FOR lExpression1] [WHILE lExpression2] [IN nWorkArea | cTableAlias]
   [NOOPTIMIZE]

Parameters

  • FieldName1 WITH eExpression1 [, FieldName2 WITH eExpression2 ... ]
    Specifies that the data in FieldName1 be replaced with the value of the expression eExpression1; that the data in FieldName2 be replaced with the value of the expression eExpression2; and so on.

    When the expression value is longer than the width of a numeric field, REPLACE forces the value to fit by carrying out the following steps:

    • First, REPLACE truncates decimal places and rounds the remaining decimal portion of the field.
    • If the value still doesn't fit, REPLACE stores the field contents using scientific notation.
    • If the value still doesn't fit, REPLACE replaces the field contents with asterisks.

  • ADDITIVE
    Appends to the end of the memo fields replacements to memo fields. ADDITIVE applies to replacements in memo fields only. If you omit ADDITIVE, the memo field is overwritten with the value of the expression.

  • Scope
    Specifies a range of records to replace. The default scope for REPLACE is the current record (NEXT 1).

    Only the records that fall within the range are replaced. The scope clauses are: ALL, NEXT nRecords, RECORD nRecordNumber, and REST. For more information on scope clauses, see Scope Clauses.

  • FOR lExpression1
    Specifies that the designated fields be replaced only in records for which lExpression1 evaluates to true (.T.). Including FOR makes it possible for you to conditionally replace records, filtering out those you don't want replaced.

    Rushmore Query Optimization optimizes REPLACE FOR if lExpression1 is an optimizable expression. For best performance, use an optimizable expression in the FOR clause.

    For more information, see SET OPTIMIZE and Using Rushmore Query Optimization to Speed Data Access.

  • WHILE lExpression2
    Specifies a condition whereby fields are replaced for as long as the logical expression lExpression2 evaluates to true (.T.).

  • IN nWorkArea
    Specifies the work area of the table in which records are updated.

  • IN cTableAlias
    Specifies the alias of the table in which records are updated.

    If you omit nWorkArea and cTableAlias, records are updated in the table in the currently selected work area.

  • NOOPTIMIZE
    Prevents Rushmore optimization.

    For more information, see SET OPTIMIZE and Using Rushmore Query Optimization to Speed Data Access.

Remarks

REPLACE replaces data in a field with the value in an expression. Fields in unselected work areas must be prefaced with their alias.

Note   If the IN clause is omitted, no replacement occurs if the record pointer is at the end of the file in the current work area and you specify a field in another work area.

Example

The following example creates a table with 10 records. REPLACE is used to place random values into a field. MIN( ) and MAX( ) displays the maximum and minimum values in the table.

CLOSE DATABASES
CREATE TABLE Random (cValue N(3))
FOR nItem = 1 TO 10  && Append 10 records,
   APPEND BLANK
   REPLACE cValue WITH 1 + 100 * RAND( )  && Insert random values
ENDFOR

CLEAR
LIST  && Display the values
gnMaximum = 1  && Initialize minimum value
gnMinimum = 100  && Initialize maximum value
SCAN 
   gnMinimum = MIN(gnMinimum, cValue)
   gnMaximum = MAX(gnMaximum, cValue)
ENDSCAN
? 'The minimum value is: ', gnMinimum  && Display minimum value
? 'The maximum value is: ', gnMaximum  && Display maximum value

See Also

GATHER | INSERT - SQL | SCATTER