How to Use the UPDATE Command

ID: Q95665

The information in this article applies to:

Microsoft Visual FoxPro for Windows, versions 3.0, 6.0
Microsoft FoxPro for Windows, version 2.5x
Microsoft FoxPro for MS-DOS, versions 1.01, 1.02, 2.0, 2.5, 2.5a
Microsoft FoxBASE+ for MS-DOS, version 2.1
Microsoft FoxPro for Macintosh, versions 2.5x, 2.6a
Microsoft FoxBASE+ for the Macintosh, version 2.0

SUMMARY

The UPDATE command updates the currently selected database file with data from a database open in another work area. The current database must be indexed or sorted on the key field. Data is replaced for records with matching key fields. The FROM database must also be indexed or sorted unless the RANDOM option is specified.

MORE INFORMATION

For each record in the currently selected database, there may be multiple matching records in the update database. If this is the case, the record in the currently selected database is updated by each of these matching records. After the update is completed, the record in the currently selected database will contain the data from the last matching record in the update database.

Also, if the currently selected database contains records with duplicate key fields, only the first record is updated.

Example


   SELECT 1
   USE invoices INDEX invoices
   SELECT 2
   USE detail
   SELECT 1
   UPDATE ON ino FROM detail ;
   REPLACE itotal WITH detail.qty * detail.price RANDOM

In the above example, the key field is INO. The INVOICES database is indexed on INO. The detail database is not indexed, so the RANDOM clause is used with the UPDATE command. If the RANDOM clause is not used, the INVOICES database will not be updated properly because the UPDATE command is expecting the DETAIL database to be in indexed order. The UPDATE command looks for a matching record in the DETAIL database; when it finds one, it replaces ITOTAL with the result of qty * price. In the DETAIL database, there are multiple matching records, so the INVOICES database will be updated only with data from the last matching record.

NOTE: The key field can be only one field. Two fields cannot be concatenated together, as in INO+ITOTAL.

WARNING: SET DELETED ON ignores deleted records in the source file (DETAIL database), but deleted records in the target file (INVOICES) are updated.

Additional query words: VFoxWin FoxDos FoxWin 2.50b 2.50c

Keywords : FxprgGeneral
Version : 2.5x 3.00 | 1.01 1.02 2.00 2.50
Platform : MACINTOSH MS-DOS WINDOWS
Issue type :