Microsoft KB Archive/95665

= How to Use the UPDATE Command =

Article ID: 95665

Article Last Modified on 11/17/2003

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft Visual FoxPro 6.0 Professional Edition
 * Microsoft FoxBASE+ 2.5 for MS-DOS

-



This article was previously published under Q95665



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



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

Also, if the currently selected table 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 table is indexed on INO. The detail table is not indexed, so the RANDOM clause is used with the UPDATE command. If the RANDOM clause is not used, the INVOICES table will not be updated properly because the UPDATE command is expecting the DETAIL table to be in indexed order. The UPDATE command looks for a matching record in the DETAIL table; when it finds one, it replaces ITOTAL with the result of qty * price. In the DETAIL table, there are multiple matching records, so the INVOICES table 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 table), but deleted records in the target file (INVOICES) are updated.

Keywords: KB95665

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.