delete

The EGL delete statement removes either a record from a file or a row or a segment from a DL/I database.

Syntax diagram for the delete statement
record name
Name of the I/O object: a DLISegment, indexed, relative, or SQL record being deleted
usingPCB pcbName
Option allowing you to specify the name of a PCB, as defined in your PSB record, to use instead of the default PCB.
with #dli{ dliStatement }
Option that allows an explicit DL/I DLET call, as described in #dli directive. Leave no space between #dli and the left brace.
from resultSetID
ID that ties the delete statement to a get or open statement run earlier in the same program. For details, see resultSetID.

An example is as follows:

  if (userRequest == "D")
    try
      get myRecord forUpdate;
      onException 
        myErrorHandler(12);   // exits the program
    end

    try
      delete myRecord;
      onException 
        myErrorHandler(16);
    end
  end

The behavior of the delete statement depends on the record type. For details on DL/I processing, see DLISegment record. For SQL processing, see SQL record.

DLISegment record

The delete statement generates a DL/I DLET statement. In DL/I, you must get and hold a segment before deleting it. The EGL keywords get...forUpdate, get next...forUpdate, and get next inParent...forUpdate will all hold the requested segment for deletion.

The following example deletes an order from the customer database:
	//create instances of the records
	myCustomer CustomerRecord;
	myLocation LocationRecord;
	myOrder    OrderRecord;

	//build a segment search argument
	myCustomer.customerNo = "005001";
	myLocation.locationNo = "000022";
	myOrder.orderDateNo = "20050730A003";

	//hold the requested order record
	try
		get myOrder forUpdate;
		onException
			myErrorHandler(2);
	end
	
	//delete the order
	try
		delete myOrder;
		onException
			myErrorHandler(7);
	end
The get...forUpdate statement generates qualified SSAs for customer, location, and order:
GHU STSCCST (STQCCNO = :myCust.customerNo) 
    STSCLOC (STQCLNO = :myLocation.locationNo) 
    STPCORD (STQCODN = :myOrder.orderDateNo)
The delete statement then simply generates the following:
DLET STPCORD

Indexed or relative record

If you want to delete an indexed or relative record, do as follows:
  • Issue a get statement for the record and specify the forUpdate option
  • Issue the delete statement, with no intervening I/O operation against the same file
After you issue the get statement, the effect of the next I/O operation on the same file is as follows:
  • If the next I/O operation is a replace statement on the same EGL record, the record is changed in the file
  • If the next I/O operation is a delete statement on the same EGL record, the record in the file is marked for deletion
  • If the next I/O operation is a get on the same file (with the forUpdate option), a subsequent replace or delete is valid on the newly read file record
  • If the next I/O operation is a get on the same EGL record (with no forUpdate option) or is a close on the same file, the file record is released without change

For details on the forUpdate option, see get.

SQL record

In the case of SQL processing, you must use the forUpdate option on an EGL get or open statement to retrieve a row for subsequent deletion:
  • You can issue a get statement to retrieve the row; or
  • You can issue an open statement to select a set of rows and then invoke a get next statement to retrieve the row of interest.
In either case, the EGL delete statement is represented in the generated code by an SQL DELETE statement that references the current row in a cursor. You cannot modify that SQL statement, which is formatted as follows:
  DELETE FROM tableName
    WHERE CURRENT OF cursor

If you wish to write your own SQL DELETE statement, use the EGL execute statement.

You cannot use a single EGL delete statement to remove rows from multiple SQL tables.

Feedback
(C) Copyright IBM Corporation 2000, 2005. All Rights Reserved.