Microsoft KB Archive/116037

= How to Perform REPLACE for Two Databases w/ Common Field =

Article ID: 116037

Article Last Modified on 10/15/2003

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft FoxPro 2.6a Professional Edition for Macintosh

-



This article was previously published under Q116037



SUMMARY
To update more than one record in a database from another, similar database, you may need to check for an end-of-file (EOF) marker during each iteration of the REPLACE operation. This is best accomplished with a DO ... WHILE loop, as demonstrated below.

NOTE: In order for this process to work, the two databases must have a common key field.



MORE INFORMATION
To demonstrate this process, we'll use the CUSTOMER database in the FoxPro TUTORIAL subdirectory. First, we'll copy CUSTOMER to a new database called NEWCUST, then we'll delete the contents of the COMPANY field, and then we'll restore the contents of the COMPANY field using the COMPANY field information in CUSTOMER.   Set the default directory to the location of CUSTOMER.DBF. For example, in FoxPro 2.x, issue the following command in the Command window: SET DEFAULT TO C:\\TUTORIAL. In Visual FoxPro, Issue the following: SET DEFAULT TO C:\\SAMPLES\DATA NOTE: In Visual FoxPro, you will also need to change all references below from from CNO to CUST_ID.   In the Command window, issue the following commands: USE customer COPY TO newcust SELECT 0 USE newcust ** the following command has no spaces between the quotation marks REPLACE ALL company WITH "" ** BROWSE the table with the empty COMPANY field contents BROWSE CLEAR ALL CLOSE ALL ** open a new program file called TEST ** if a program called TEST already exists, choose another name MODIFY COMMAND test   In the edit window for the TEST program, type the following commands: SELECT 0 USE customer SELECT 0 USE newcust

SELECT customer

** while not at end of file of CUSTOMER, do some actions DO WHILE !EOF ** select the destination table SELECT newcust ** replace the empty COMPANY field with data from CUSTOMER where **  the common key field is the same REPLACE newcust.company WITH customer.company; FOR newcust.cno = customer.cno ** select the source table again to go to next record SELECT customer SKIP ENDDO SELECT newcust ** BROWSE the table with the newly filled COMPANY data BROWSE NOTE: If the first database (that is, CUSTOMER) is indexed on the common key field between the two tables, replace the commands in step 3 with the following: SELECT 0 USE customer ORDER TAG cno SELECT 0 USE newcust REPLACE ALL newcust.company WITH customer.company FOR ; seek(cno,"customer")  Save TEST.PRG and then run it.

