UPDATE

In this section:

How to:

Reference:

The UPDATE command writes new values to data source fields using data from a stack or the Current Area. All of the fields must be in the same data source path. The key fields in the stack or Current Area identify which segment instances to update.

The segment containing the first update field is called the anchor. If the anchor segment is not the root, you must establish a current instance in each of the ancestor segments of the anchor, or provide ancestor segment key values in the source stack or Current Area. This enables UPDATE to navigate from the root to the first instance of the anchor segment.

Syntax: How to Use the UPDATE Command

The syntax of the UPDATE command is 

[FOR {int|ALL}] UPDATE fields [FROM stack[(row)]] [;]

where:

FOR

Is used with int or ALL to specify how many rows of the stack to use to update the data source. When FOR is used, a FROM stack must be supplied. If no FOR prefix is used, the UPDATE works the same way that FOR 1 UPDATE works.

int

Is an integer constant or variable that indicates the number of rows to use to update the data source.

ALL

Specifies that the entire stack is used to update the corresponding records in the data source.

fields

Is used to specify every data source field to update. You cannot update key fields. All fields must be in the same path.

FROM

Is used to specify a stack containing records to insert. If no stack is specified, data from the Current Area is used.

stack

Is the name of the stack whose data is used to update the data source. Only one stack can be specified.

row

Is a subscript that specifies the first stack row to use to update the data source.

;

Terminates the command. Although the semicolon is optional, including it to allow for flexible syntax and better processing is recommended. For more information about the semicolon, see Terminating a Command's Syntax.

Example: Using UPDATE

The UPDATE command can be executed after a MATCH command finds a matching record. For example: 

MATCH Emp_ID;
ON MATCH UPDATE Department Curr_Sal Curr_Jobcode Ed_Hrs FROM Chgemp;

Consider an application used when an employee changes his or her last name. The application user is prompted for the employee ID and new last name in a form. The user enters the name and triggers the ChngName function. If the employee is in the data source, ChngName updates the data source. If the employee is not in the data source, ChngName displays a message asking the user to try again. 

CASE ChngName
REPOSITION Emp_ID;
MATCH Emp_ID;
ON MATCH BEGIN
   UPDATE Last_Name;
   COMMIT;
   Winform Close;
   ENDBEGIN
ON NOMATCH BEGIN
   TYPE "Employee ID <Emp_ID was not found"
        "Try again";
   ENDBEGIN
ENDCASE

The command can also be issued without a preceding MATCH. In this situation, the key field values are taken from the FROM stack or the Current Area and a MATCH is issued internally. When a set of rows is changed without first finding out if the set already exists in the data source, it is possible that some of the rows in the stack will be rejected. Upon the first rejection, the process stops and the rest of the set is rejected. For all rows to be accepted or rejected as a unit, the set should be treated as a logical unit of work, and a ROLLBACK issued if the entire set is not accepted.

Reference: Usage Notes for UPDATE

  • Key fields cannot be updated.
  • There can only be one input or FROM stack in an UPDATE command.
  • When an UPDATE command is complete, the variable FocError is set. If the UPDATE is successful, FocError is set to zero (0). If the records do not exist, and are therefore unchanged, FocError is set to a non-zero value and (if the UPDATE is set-based) FocErrorRow is set to the number of the row that failed.
  • Maintain Data requires that the data sources to which it writes have unique keys.

Reference: Commands Related to UPDATE

  • COMMIT. Makes all data source changes since the last COMMIT permanent.
  • ROLLBACK. Cancels all data source changes made since the last COMMIT.

Update and Transaction Variables

After the UPDATE is processed, the internal variable FocError is given a value. If the UPDATE is successful, FocError is zero (0). If the UPDATE fails (that is, the key values did not exist in the data source) FocError is set to a non-zero value, and (if the UPDATE was set-based) FocErrorRow is set to the number of the row that failed. If at COMMIT time there is a concurrency conflict, FocError and the internal variable FocCurrent are set to non-zero values.

Example: Using Stacks

In the following example, the user enters many employee IDs and new names at one time. Rather than performing a MATCH on each row in the stack, this function checks FocError after the UPDATE command. If FocError is zero (0), a COMMIT is issued and the function is exited. If FocError is non-zero, another function, which tries to clean up the data, is performed. The IF command, which starts at the beginning of the function, checks to see whether there are any rows in the stack. If the stack does contain have any rows, a form displays allowing the user to enter new data. If the stack contains rows, the user has made a mistake, so a different form displays allowing the user to edit the entered data.

The Maintain Data procedure contains:

STACK CLEAR Namechng;
PERFORM Chngname;
CASE Chngname
IF Namechng.FocCount LE 0 
   THEN Winform Show Myform1;
   ELSE Winform Show Myform2;
FOR ALL UPDATE Last_Name FROM Namechng;
IF FocError EQ 0 BEGIN
   COMMIT;
   GOTO ENDCASE;
   ENDBEGIN
PERFORM Fixup;
GOTO Chngname;
ENDCASE

Data Source Position

A Maintain Data procedure always has a position either in a segment or before the beginning of the chain. If positioned within a segment, the position is the last record successfully retrieved on that segment. If a retrieval operation fails, then the position of the data source remains unchanged.

If an UPDATE is successful, the data source position is changed to the last record it updated. If an UPDATE fails, the position is at the end of the chain because the MATCH prior to the UPDATE also fails.

Unique Segments

The UPDATE command treats fields in unique segments the same as fields in other types of segments.