DELETE

How to:

Reference:

The DELETE command identifies segment instances from a transaction source (a stack or the Current Area) and deletes the corresponding instances from the data source.

When you issue the command, you specify an anchor segment. For each row in the transaction source, DELETE searches the data source for a matching segment instance. When it finds a match, it deletes that anchor instance and all the descendants of 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. This ensures that DELETE can navigate from the root to the first instance of the anchor segment.

Syntax: How to Use the DELETE Command

The syntax of the DELETE command is 

[FOR {int|ALL}] DELETE segment [FROM stack[(row)]] [;]

where:

FOR

Is used with ALL or an integer to specify how many stack rows to use to identify segment instances. If FOR is omitted, one stack row will be used.

When you specify FOR, you must also specify FROM to identify a source stack.

int

Is an integer constant or variable that indicates the number of stack rows to use to identify segment instances to be deleted.

ALL

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

segment

Specifies the anchor segment of the path to delete. To specify a segment, provide the name of the segment or of a field within the segment.

FROM

Is used to specify a stack whose key columns identify records to delete. If no stack is specified, data from the Current Area is used.

stack

Is a stack name. Only one stack can be specified.

row

Is a subscript that specifies which stack row to begin with.

;

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

Example: Specifying Which Segments to Delete

The DELETE command removes the lowest specified segment and all of its descendant segments. For example, if a data source structure has four segments in a single path (named First, Second, Third, and Fourth), the command 

DELETE First.Field1 Second.Field2;

will delete instances from the Second, Third, and Fourth segments.

If you issue the command 

DELETE First.Field1;

you will delete the entire data source path.

Example: Deleting Records Identified in a Stack

In the following example, the data in rows 2, 3, and 4 of the Stkemp stack is used to delete data from the data source. The stack subscript indicates start in the second row of the stack and the FOR 3 means DELETE data in the data source based on the data in the next three rows. 

FOR 3 DELETE Emp_ID FROM Stkemp(2);

Example: Deleting a Record Identified in a Form

The first example prompts the user for the employee ID in the EmployeeIDForm form. If the employee is already in the data source, all records for that employee are deleted from the data source. This includes the employee instance in the root segment and all descendent instances, such as pay dates, addresses, and so on. In order to find out if the employee is in the data source, a MATCH command is issued: 

MAINTAIN FILE Employee
Winform Show EmployeeIDForm;
CASE DELEMP
MATCH Emp_ID; 
ON MATCH DELETE Emp_ID;
ON NOMATCH TYPE "Employee id <Emp_ID not found. Reenter";
COMMIT;
ENDCASE
END

When the user presses Enter, function DELEMP is triggered as an event handler from a form. Control is then passed back to EmployeeIDForm.

The second example provides the same functionality. The only difference is that a MATCH is not used to determine whether the employee already exists in the data source. The DELETE can only work if the record exists. Therefore, if an employee ID is entered that does not exist, the only action that can be taken is to display a message. In this case, the variable FocError is checked. If FocError is not equal to zero (0), then the DELETE failed and a message displays: 

MAINTAIN FILE Employee
INFER EMP_ID INTO STACKEMP
Winform Show EmployeeIDForm;
CASE DELEMP
DELETE Emp_ID;
IF FocError NE 0 THEN
 TYPE "Employee id <Stackemp.Emp_ID not found.  Reenter";
COMMIT;
ENDCASE
END

Reference: Usage Notes for DELETE

  • Because the DELETE command removes the instance pointed to by the segment position marker, after the deletion, the marker has a null value and the segment has no current position. To reestablish position, you can issue the REPOSITION command.
  • You delete a unique segment by deleting its parent. To erase the fields of a unique segment without affecting its parent, you can instead update its fields to space, zero (0), or null.
  • In order for the DELETE to work, the data must exist in the data source. When a set of rows are changed without first finding out if they already exist in the data source, then 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. If you want all rows to be accepted or rejected as a unit, you should treat the stack as a logical transaction, evaluate the FocError transaction variable, and then issue a ROLLBACK command if the entire stack is not accepted. The transaction variable FocErrorRow is automatically set to the number of the first row that failed.
  • After the DELETE is processed, the transaction variable FocError is given a value. If the DELETE is successful, FocError is zero (0). If the DELETE fails (for example, the key values do not exist in the data source), FocError is set to a non-zero value and (if the DELETE is set-based) FocErrorRow is set to the number of the row that failed. If there is a concurrency conflict at COMMIT time, the transaction variable FocCurrent is set to a non-zero value.
  • A DELETE command cannot have more than one input (FROM) stack.
  • When a DELETE command is complete, the variable FocError is set. If the DELETE is successful (the records to be deleted exist in the data source), then FocError is set to zero (0). If the records do not exist, FocError is set to a non-zero value. If the DELETE operation was set-based, Maintain Data sets FocErrorRow 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 DELETE

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