How to Update a Master File from a Transaction File

Article translations Article translations
Article ID: 98355 - View products that this article applies to.
This article was previously published under Q98355
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

SUMMARY

The FoxPro UPDATE command allows existing records in a database to be updated from information contained in a second database, based on a common key field between the two databases. The UPDATE command does not allow new records in the second database to be automatically added to the first database. The following procedure can be used to update existing records as well as add new records.

MORE INFORMATION

The following procedure uses the UPDATE command to first update existing records. A SELECT SQL command determines which records in the new data file are not contained in the original data file. An APPEND command appends the results of the SELECT command into the original database.
   * oldfile = existing .DBF file
   * newfile = .DBF with updated and new records to add to oldfile

   * NOTE: For the UPDATE command to be successful, both databases
   * must have a key field in common and be ordered on that key field.

   SELECT A
      USE oldfile ORDER keyfield
   SELECT B
      USE newfile ORDER keyfield
   SELECT oldfile

   * Note: The following command must be edited to allow for
   * replacing each field in the oldfile DBF with the corresponding
   * field in the newfile DBF.

   UPDATE ON keyfield FROM newfile ;
      REPLACE oldfile.field1 WITH newfile.field1, ;
      oldfile.field2 WITH newfile.field2, ;
      oldfile.field3 WITH newfile.field3

   SELECT *;
      FROM newfile ;
      INTO TABLE myupdate ;
      WHERE newfile.keyfield NOT IN ;
            (SELECT oldfile.keyfield FROM oldfile)
   SELECT oldfile
   APPEND FROM myupdate
   SELECT myupdate
   USE
   SET SAFETY OFF
   DELETE FILE myupdate
   SET SAFETY ON
				
For more information, see the SELECT, SELECT SQL, UPDATE, and APPEND FROM commands in the FoxPro 2.0 "Commands & Functions" manual or the FoxPro 2.5 "Language Reference."

Properties

Article ID: 98355 - Last Review: February 28, 2014 - Revision: 2.1
APPLIES TO
  • Microsoft Visual FoxPro 3.0 Standard Edition
  • Microsoft Visual FoxPro 6.0 Professional Edition
  • Microsoft FoxPro 2.0
  • Microsoft FoxPro 2.5b for MS-DOS
  • Microsoft FoxPro 2.5a
  • Microsoft FoxPro 2.5b
  • Microsoft FoxPro 2.5a
Keywords: 
kbnosurvey kbarchive KB98355

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com