How to Use the UPDATE Command

 Last reviewed: April 29, 1996
Article ID: Q95665
The information in this article applies to:
  • Microsoft Visual FoxPro for Windows, version 3.0
  • Microsoft FoxPro for Windows, versions 2.5x
  • Microsoft FoxPro for MS-DOS, versions 1.01, 1.02, 2.0, 2.5, and 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 reference words: VFoxWin 3.00 FoxDos FoxWin 1.01 1.02 2.00 2.10
2.50 2.50a 2.50b 2.50c 2.60a
KBCategory: kbprg
KBSubcategory: FxprgGeneral

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 29, 1996
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.