This article was previously published under Q98355
This article has been archived. It is offered "as is" and will no longer be updated.
The FoxPro UPDATE command allows existing records in a database to beupdated from information contained in a second database, based on a commonkey field between the two databases. The UPDATE command does not allow newrecords in the second database to be automatically added to the firstdatabase. The following procedure can be used to update existing records aswell as add new records.
The following procedure uses the UPDATE command to first update existingrecords. A SELECT SQL command determines which records in the new data fileare not contained in the original data file. An APPEND command appends theresults 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 FROMcommands in the FoxPro 2.0 "Commands & Functions" manual or the FoxPro 2.5"Language Reference."