Wiki Home

Replace Command


Namespace: Wiki
The REPLACE command lets you fill in a set of fields with specified values. It can operate on a specified record, the current record, a range of records (Scope Clauses) or a set of records (FOR Contactdate = DATE()). and even multiple related tables simultaneously.

REPLACE apparently has a limitation of REPLACEing only up to 128 fields in a single command. I didn't find this documented in the VFP 7.0 or 8.0 help files or a few other places I looked. -- Andy Needham
If you attempt to REPLACE more than 128 fields in a single command, it will generate a Syntax Error -- Mike Yearwood

I generally use the command like this...

REPLACE field1 WITH expression1, field2 WITH expression2 Scope Clauses IN alias

OR

SELECT alias
REPLACE field1 WITH expression1, field2 WITH expression2

I format the command to permit me to reorganize the field sequence if needed. I find each part of the command easier to read this way especially when complex expressions are used.
REPLACE ;
   firstname WITH "Mike", ;
   lastname WITH "Yearwood" ;
   IN contacts


While upgrading a FoxPro 2.6 app to VFP9, I discovered the comma is NOT needed! And probably hasn't been needed since 2.6 ! Think of how many commas (in bytes) we've wasted! Craig Roberts

REPLACE ;
   firstname WITH "Mike" ;
   lastname WITH "Yearwood" ;
   phone with "123-456-0123" ;
   IN contacts


A benefit to this method is that (in VFP6sp5 and newer... It probably worked in some earlier sp's and maybe VFP5, but I'm almost certain not in VFP3) you can add comments on each line of the replace:

I would advise against it because it makes code much harder to read.

REPLACE FirstName     with "One", ; && Person's First Name
        MiddleInitial with "two", ; && Added Jan 4, 2003
        LastName      with "three"  && Person's Last Name

Actually that capability came with FPD 2.6 I believe. As regards "much harder to read", that is highly debatable and subjective. The example shown doesn't print nicely here on Wiki, and it is overkill in my opinion, but the comment beside "two" can be most enlightening in later work. - Jim Nelson

I recall reading somewhere that VFP started supporting that with VFP 6. However, according to VFP help, "You cannot place && and a comment after the semicolon used to continue a command line to an additional line." This is quite definitely false, in VFP 6, 7, and 8. While it could be hard to read, what is easier, exactly? Here's an example of how it could possibly be made easier to read:

USE ImportTable
REPLACE ;
    ImportTable.Field1 WITH "Peter", ; && First Name
    ImportTable.Field2 WITH "Q", ;     && Middle Initial
    ImportTable.Field3 WITH "Smith"    && Last Name
-- Peter Crabtree
The following are alternatives that work with comments on why they should be avoided.

1)
REPLACE firstname WITH "Mike"
REPLACE lastname WITH "Yearwood"

While this will obviously produce the same result, it's my belief that VFP must be reading the record into memory, updating the specified field with the specified data and then posting this update back to the table. Timing shows the two commands above take longer than the single command. However, when using complex expressions, it's sometimes easier to write the code in single expressions like this. Still, especially in loops or with large numbers of records to update, debug the code like this, but recombine the code later.

Of course, during development you may wish to keep the REPLACE of each field separate, since in the prior example the REPLACE is one line... and a data type mismatch on a single line REPLACE with a dozen or so fields doesn't help you. However, if each REPLACE is a separate line, the error will occur on that specific line. So, after testing, go back and reformat your REPLACEs to use one line, because it is faster.

2)
REPLACE contacts.firstname WITH "Mike", contacts.lastname WITH "Yearwood"

Just recently, I debugged someone else's code that used this construct. If Contacts is not the current alias, this syntax can lead to unexpected results. For an example try this...

USE sometable
SELECT 0
REPLACE sometable.somefield WITH somevalue

VFP should prompt you for a table. That's because the REPLACE command is trying to operate on the current alias and no table is open in the current alias.

If Contacts is the current alias, this syntax, while more specific, is just redundant. VFP supports this construct because you might have related 2 tables and have a desire to update records in both tables at one time. So, VFP must be wasting time looking for the Contacts alias. -- Mike Yearwood

Here's a good reason to use the replace alias.fieldname construct (but still use the IN clause)! Suppose you have two cursors, one is a subset of rows and columns (like for a navigation list/grid) and the other contains all fields/rows from the table for data entry. The goal is to update the data entry cursor and the navigation cursor simultaneously. Update SQL cannot do that!

SET ORDER TO Some_PK IN c_DataEntry
SELECT c_Navigation
SET RELATION TO Some_PK INTO c_DataEntry
REPLACE ALL ;
  c_DataEntry.cLastName WITH "LastName", ;
  c_DataEntry.cFirstName WITH "FirstName", ;
  c_Navigation.cFullName WITH c_DataEntry.cLastName + ", " + c_DataEntry.cFirstName ;
  IN c_Navigation


That will update the records in the data entry cursor first, then those in the navigation cursor (assuming there was an earlier query). You could set sendupdates to .F. on the navigation cursor and do a tableupdate on the data entry cursor. -- Mike Yearwood
Don't forget the all-important note from the bottom of Help (where lots of people miss it):

"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."
-- Cindy Winegarden
Indeed. Do not forget this - it can cause much frustration. Rule of thumb:
ALWAYS use the "IN" clause for REPLACE statements. -- Peter Crabtree
--
I don't like using the "IN" clause as it makes me lazy about SELECTing the correct work area. My rule of thumb is always SELECT the correct area first and never use the "IN" clause. If you use the IN clause and the current work area is EOF or empty for example no records will be updated in the target table. This is usually unexpected and has certainly caught me out. -- Darren Woodford
See Update SQL
REPLACE is supposed to be faster than UPDATE -- Alex Feldstein

It clearly is, especially when used in "batch" mode. My tests show that REPLACE in batch mode is about 5 times as fast as REPLACing one field at a time, and about 6 times as fast as UPDATE. Perhaps UPDATE is slower because it needs to check to see if the table is open and open it each time?

Per discussion on the Pro Fox listserve, the following code has been changed to be more fair to UPDATE by including a SEEK for the test record for each REPLACE set. However, doing this actually made BOTH Tests 1 AND 3 run slower--while Test 2 -- batch REPLACE -- remains the winner at about 1 second on average. -- Ken Dibble

CLEAR

ERASE junk.*

SET TALK OFF

? "RUNNING"
? ""

CREATE TABLE junk ( ;
     RecID I, ;
     My1 C(25), ;
     My2 C(25), ;
     My3 C(25), ;
     My4 C(25), ;
     My5 C(25), ;
     My6 C(25), ;
     My7 C(25), ;
     My8 C(25), ;
     My9 C(25), ;
     My10 C(25) )

INDEX ON RecID TAG RecID

INSERT INTO junk (RecID,My1,My2,My3,My4,My5,My6,My7,My8,My9,My10) ;
VALUES (1," "," "," "," "," "," "," "," "," "," ")

SELECT junk
USE

LOCAL oldseconds, timetotal1, timetotal2, timetotal3, timetotal4, thevalue

* TEST 1: Averages about 5.5 seconds.

oldseconds = SECONDS()

USE junk IN 0

FOR x = 1 TO 10000
    thevalue = "Thing" + TRANSFORM(x)
    SEEK 1 ORDER RecID
    IF FOUND()
         REPLACE My1 WITH thevalue
         REPLACE My2 WITH thevalue
         REPLACE My3 WITH thevalue
         REPLACE My4 WITH thevalue
         REPLACE My5 WITH thevalue
         REPLACE My6 WITH thevalue
         REPLACE My7 WITH thevalue
         REPLACE My8 WITH thevalue
         REPLACE My9 WITH thevalue
         REPLACE My10 WITH thevalue
    ENDIF
ENDFOR

USE

timetotal1 = SECONDS() - oldseconds


USE junk IN 0
GO 1
REPLACE My1 WITH " "
REPLACE My2 WITH " "
REPLACE My3 WITH " "
REPLACE My4 WITH " "
REPLACE My5 WITH " "
REPLACE My6 WITH " "
REPLACE My7 WITH " "
REPLACE My8 WITH " "
REPLACE My9 WITH " "
REPLACE My10 WITH " "
USE

* TEST 2: Averages about 1 second.

oldseconds = SECONDS()

USE junk IN 0

FOR x = 1 TO 10000
    thevalue = "Thing" + TRANSFORM(x)
    SEEK 1 ORDER RecID
    IF FOUND()
         REPLACE My1 WITH thevalue, My2 WITH thevalue, My3 WITH thevalue, ;
         My4 WITH thevalue, My5 WITH thevalue, My6 WITH thevalue, ;
         My7 WITH thevalue, My8 WITH thevalue, My9 WITH thevalue, ;
         My10 WITH thevalue
    ENDIF
ENDFOR

USE

timetotal2 = SECONDS() - oldseconds

USE junk IN 0
GO 1
REPLACE My1 WITH " "
REPLACE My2 WITH " "
REPLACE My3 WITH " "
REPLACE My4 WITH " "
REPLACE My5 WITH " "
REPLACE My6 WITH " "
REPLACE My7 WITH " "
REPLACE My8 WITH " "
REPLACE My9 WITH " "
REPLACE My10 WITH " "
USE

* TEST 3: Averages about 8.5 seconds!

oldseconds = SECONDS()

* Even if you provide a "boost" by uncommenting the commented lines
* here, at best you save .5 seconds over the full run.
*USE junk IN 0
FOR x = 1 TO 10000
    thevalue = "Thing" + TRANSFORM(x)
    *SEEK 1 ORDER RecID
    *IF FOUND()
    UPDATE junk ;
    SET My1 = thevalue, My2 = thevalue, My3 = thevalue, ;
    My4 = thevalue, My5 = thevalue, My6 = thevalue, My7 = thevalue, ;
    My8 = thevalue, My9 = thevalue, My10 = thevalue WHERE RecID = 1
    *ENDIF
ENDFOR

USE

timetotal3 = SECONDS() - oldseconds

* TEST 4: ONE Replace - As fast as Test 2

oldseconds = SECONDS()

USE junk IN 0

FOR x = 1 TO 10000
    thevalue = "Thing" + TRANSFORM(x)
    SEEK 1 ORDER RecID
    IF FOUND()
         REPLACE My1 WITH thevalue, ;
                 My2 WITH thevalue, ;
                 My3 WITH thevalue, ;
                 My4 WITH thevalue, ;
                 My5 WITH thevalue, ;
                 My6 WITH thevalue, ;
                 My7 WITH thevalue, ;
                 My8 WITH thevalue, ;
                 My9 WITH thevalue, ;
                 My10 WITH thevalue
    ENDIF
ENDFOR

USE

timetotal4 = SECONDS() - oldseconds

USE junk IN 0
GO 1
REPLACE My1 WITH " "
REPLACE My2 WITH " "
REPLACE My3 WITH " "
REPLACE My4 WITH " "
REPLACE My5 WITH " "
REPLACE My6 WITH " "
REPLACE My7 WITH " "
REPLACE My8 WITH " "
REPLACE My9 WITH " "
REPLACE My10 WITH " "
USE

? "TEST 1: " + TRANSFORM(timetotal1)
? "TEST 2: " + TRANSFORM(timetotal2)
? "TEST 3: " + TRANSFORM(timetotal3)
? "TEST 4: " + TRANSFORM(timetotal4)


ERASE junk.*

SET TALK ON

-- Ken Dibble

Why are you testing multiple replace commands against a single UPDATE - SQL command. Wouldn't it be more accurate to test mutliple UPDATE - SQLs against multiple replaces? -- Mike Yearwood

I don't see why it would be more accurate. The amount of work being done by both commands is the same in my test. That's the real-world goal, right? To get the work done?

By the way--your reply didn't trigger an appearance in the "Topics Changed..." section of the Wiki homepage; if it had I would have replied immediately. I just spotted this (on 10/16/05) while searching for my name on something I posted a few days ago.

-- Ken Dibble
UPDATE SET field1 as value1, field2 as value2, field3 as value3, field4 as value4 where condition


is not the same as

replace field1 with value1 where condition
replace field2 with value2 where condition
replace field3 with value3 where condition
replace field4 with value4 where condition


I just don't see the point of providing a test contrasting several replace commands against a single UPDATE-SQL command. Unless you're trying to demonstrate that a single replace is faster than multiple replaces while also trying to show the differences between update and replace? -- Mike Yearwood

Test 1 is really unrelated to the rest; it demonstrates that it's a lot faster to do:

REPLACE field1 WITH value1, field2 WITH value2, field3 WITH value3...

than it is to do:

REPLACE field2 WITH value1
REPLACE field2 WITH value2
REPLACE field3 WITH value3....

Also, it looks like Test 4 duplicates Test 2 and I don't remember why it's there now. Probably just bad editing on my part.

None of the tests include any instance of:

"replace field1 with value1 where condition"

In fact, that's invalid xbase syntax, isn't it? It would have to be:

REPLACE ... FOR condition

Right?

Test 2 compares a USE, SEEK and batch REPLACE of several identical fields in one record to Test 3, a batch UPDATE SQL of the same number of the same fields in the same record. That's apples to apples, in my opinion, because both methods accomplish the same task. It seems irrelevant, except perhaps on an entirely theoretical level, that one way takes three separate commands (and is still faster, even though Test 3, coming after Test 2, might have the additional advantage of some Windows caching) than the other way, which takes one. The point is the work that's getting done.

The REPLACE tests are pure xbase, USEing the table, doing a SEEK to get to the record and then REPLACEing field values.

The UPDATE test, if you don't uncomment those xbase "booster" lines, is pure SQL, in which case the only way to edit a specific existing record is to use a WHERE clause.

I rarely use conditional expressions to determine specific field values in groups of records to edit in real life applications; I simply use REPLACE to either populate all the fields of a new record or to copy back (potentially) changed values into one existing record. The WHERE clause is there in the UPDATE test because that's the SQL way. I was trying to compare apples to apples the whole way.

In xbase, when this is what you're doing, you do a SEEK to get to the record, then do the REPLACEs. You'd never do REPLACE...FOR in that scenario; there's no need to do it.

In SQL, you do an UPDATE with a conditional expression to get to the record and input the data because you have no choice. It's the only way to do it in pure SQL. However, if you uncomment those lines in Test 3, they "jump-start" the UPDATE by using a SEEK to get to the record first. As far as I know, that's not considered "best practice" when using UPDATE and most people wouldn't do it. In any case, it speeds things up some but is still slower than the pure xbase.

I can't help it that the navigational aspects of the two methods are different. My point in creating the tests is that when you do the absolute minimum in both pure xbase and pure SQL to edit the contents of an existing record, the SQL is a lot slower. That's all.

That's one of the big disadvantages of SQL as compared to xbase. In xbase, if I know which record I want to edit, I can just move the pointer there and do it. In SQL, I HAVE TO use a conditional expression to find the record. It's always going to be slower, in my opinion--except, perhaps, in really huge tables.

Naturally, as in most such benchmark tests, the large numbers of repetitions are there to get numbers big enough to notice the difference. You wouldn't do this kind of thing in tight loops of tens of thousands of reps in most situations. And that's pretty much the case with all of these kinds of speed tests--but people consider them valid anyway.

-- Ken Dibble
Name Expression also work well for the REPLACE command
cTable = "Customer"
cField = "Surname"
cValue = "Black"
replace (cField) with (cValue) in (cTable) Peter Easson
REPLACE can get dicey in error handling situations, leading to unexpected inconsistencies in your database. Consider:
REPLACE FieldA WITH valueA, ;
 FieldBad WITH valueB, ;  && error!
 FieldC WITH valueC

If the above is run, FieldA is actually replaced, while FieldC is not!! Hardly what some might expect. Even using TRY/CATCH doesn't help, unless you have some revert capability available.
-- Randy P

While I agree that it's not what some might expect, it is one of those things that "just is" and so is part of knowing the language.
And it does come in handy in helping to isolate which field had the problem, given the helpfulness of the error message. -- Jim Nelson


I remember seeing this at one point; however, I do not see this behavior in VFP7 or VFP9 anymore. Am I missing something?
CREATE CURSOR myCursor (firstname c(20), lastname c(20) )
APPEND BLANK
REPLACE firstname WITH "Tod" ,;
	middleinit WITH "J"  ,;
	lastname WITH "McKenna" IN myCursor

-- Tod McKenna

Category VFP Commands Category Code Samples
( Topic last updated: 2014.07.14 05:00:17 PM )