How to Use the UPDATE Command (95665)

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.