Replace

REPLACE puts data in fields. It’s the table counterpart to STORE or =, which put data in variables, but have no effect on fields. (We can’t tell you how often we’ve written code that uses = with fields and then had to figure out what was wrong with it.)

Usage

REPLACE uField1 WITH eExpr1 [ ADDITIVE ]
        [, uField2 WITH eExpr2 [ ADDITIVE ]
        [, ... ] ]
        [ Scope ] [ FOR lForExpression ]
        [ WHILE lWhileExpression ]
        [ IN cAlias | nWorkArea ]
        [ NOOPTIMIZE ]

Unless you tell it otherwise, REPLACE applies to the current record in the current work area.

eExprn must be the same or a compatible type with uFieldn. Some type conversion occurs automatically (for example, since there really is only one numeric memory variable type, from numeric to double or currency), but other conversions need to be specified explicitly (for example, character to date).

ADDITIVE is permitted only when the destination field is a memo field. It indicates that eExprn should be added to the end of the existing value of uFieldn rather than overwriting it.

The fields do not all have to be in the same table. However, there’s a gotcha here. The Scope, FOR and WHILE clauses are applied to the controlling table (usually the current work area). If you reach EOF() in that table, no further replacements take place in any table. Despite much yelling by many Xbase programmers over the years, this really isn’t a bug. It’s supposed to work this way and it’s as good a choice as any other behavior in this situation.

Here’s the deal. REPLACE lets you change fields of multiple records in multiple tables at once. Suppose you’ve written a REPLACE that moves data into records from three different tables at once. Suppose further that the REPLACE has a scope clause of NEXT 5. What if you reach the end of one of those tables before you’ve hit five records? For simplicity’s sake, the rule has always been that it’s the current work area that counts. As long as you’re not at EOF() in the current work area, the REPLACE continues (although some data may land in the bit bucket). As soon as you hit EOF() in the current work area, the REPLACE ends. No questions asked. (For explanations of Scope, FOR and WHILE, see “Xbase Xplained.”)

Prior to Visual FoxPro, the work-around for the problem was to SELECT the desired work area before the REPLACE. The addition of transactions in VFP eliminates the need for a single REPLACE to modify records in multiple tables. Together with the IN clause, there’s no reason to ever run into this problem. Use a series of REPLACE commands and have each one IN the right work area. Wrap the whole thing in a transaction and you can be pretty much assured you’re saving all the data or none of it.

See “WHILE away the Hours” in “Xbase Xplained” for another gotcha involving the WHILE clause.

NOOPTIMIZE says to turn off Rushmore before doing the replacements. We’ve never needed to do so, but if you think you might, see SET OPTIMIZE.

Example

* Two ways to update a memo field
REPLACE notes WITH notes+chr(13)+ ;
                   dtoc(date())+" Called - line was busy"
REPLACE notes WITH chr(13)+dtoc(date())+ ;
                   "Called - line was busy" ADDITIVE

* Suppose the area code for a bunch of phone numbers
* changes. You need to update those records.
* Assume you've stored the exchanges that are changing
* in a table, EXCHANGES, with one field, Exchange, and
* a tag on that field.
* The old area code is stored in cOldCode.
* The new area code is stored in cNewCode.
USE PhoneList
USE Exchanges IN 0 ORDER Exchange
SET RELATION TO LEFT(Phone,3) INTO Exchanges
REPLACE AreaCode WITH cNewCode ;
   FOR AreaCode=cOldCode ;
   AND FOUND("Exchanges")

See Also

Begin Transaction, Insert-SQL, Set Optimize, Store, Update-SQL