|Powered by QM on a Rpi server|
KnowledgeBase 00041: UPDATE.RECORD
This article was originally published as a Tip of the Week.
The UPDATE.RECORD command provides two alternative ways to modify data:
UPDATE.RECORD avoids the need to write special programs to make repetitive changes to a database. Combined with the SELECT command, apparently complex changes become extremely easy.
Batch mode applies the same update to all records or a selection of records.
The update may change any number of fields in the record and the new content may be a fixed value, the content of another field, the value of a dictionary I-type or the value of an expression entered as part of the command.
UPDATE.RECORD makes full use of the dictionary definitions of data conversion codes thus allowing users to enter data in its external format for ease of use. For example, a date can be entered as "21 Sep 11" instead of needing to calculate the internal day number (15970).
An optional report provides an audit trail of the changes made to the data and enables recovery from errors.
Examples of use are shown below....
Set field 10 of every record in a file to be "ABC".
UPDATE.RECORD filename ALL F10, "ABC"
Use a select list to change all records with field DEPT.CODE set to 20 to become 21.
SELECT STOCK WITH DEPT.CODE = "20" UPDATE.RECORD STOCK DEPT.CODE, 21
Use an EVAL expression to apply a 10% price increase.
UPDATE.RECORD STOCK ALL PRICE, EVAL "PRICE * 1.1"
Accumulate sales totals and reset the counter. Note that this example modifies two fields in each record as a single operation.
UPDATE.RECORD STOCK ALL TOTAL.SOLD, EVAL "TOTAL.SOLD + THIS.WEEK" THIS.WEEK, 0
Visual mode presents a full screen display of the external (converted) form of fields from a data record. Changes are made by moving the cursor to the desired position and entering or deleting characters. Modified lines are converted back into their internal form within UPDATE.RECORD when the cursor is moved to a new line or when the data is to be written back to the file.
The columns in the example below show the field number, its corresponding dictionary name, the text of the display name from the dictionary and the item content from the data record. There are command options to suppress some of these columns.
1: NAME : Site Name : Acme Software Limited 2: ADDRESS : Address : 42 High Street, Anytown 3: POSTCODE : Postcode : AN11 1XX 6: DATE.PAID : Date Paid : 12 Feb 98]17 Mar 98 7: INVOICES : Invoices : 001763]001966 8: LICENCES : Licences : 907881792]1907881802]1907881808 9: EXPIRED.LICENCES: Expired : 10: COUNTRY : Country : 11: CLASS : Class : 3 12: DEALER.SALES : Sales : 13: CALLBACK.DATE : Callback : 01 Jul 98 14: CALLBACK.TEXT : Callback note: Interested in new product range 15: VAT.NO : VAT no : 614 1210 25 16: SITE.TEXT : Site text : Acme Software 17: CONTACT : Contact : Anne McIntosh 18: POSITION : Position : 19: TEL.NO : Tel no : 01234-56789]01234-64526 20: FAX.NO : Fax no : 01234-21767 21: MOBILE.NO : Mobile no : 22: EMAIL : E-mail : firstname.lastname@example.org 23: SALES.TOTAL : Sales : £12783.33 24: NOTES : Notes : *CLIENTS 00106 <6,1,1> | D2DMYL[,A3] | 9R | PAYDATE | S |