delete considerations for SQL

When you use the delete statement in the context of SQL, the statement removes a row from a database. You must have previously retrieved the record using a get or open statement with the forUpdate option.

EGL uses information in your SQL record variable to create an SQL DELETE statement in your code that references the current row (where your cursor points). The following SQL is equivalent to the EGL delete statement:
  DELETE FROM tableName
    WHERE CURRENT OF cursor
You do not have the option of rewriting this statement using the #sql directive. Use the EGL execute statement to write a custom SQL DELETE statement.

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

Syntax

Syntax diagram for the delete statement
SQLRecordVariable
Refers to an area of memory where EGL has stored retrieved information from a single database row.
explicitCode
You can embed your own SQL DELETE statement to override the implicit code that EGL generates.
deleteOptions
Use the following options to control the implicit SQL code that EGL generates:
cursor
Tells EGL to create a WHERE CURRENT OF cursorID clause. You must have previously created a result set using get or open, as EGL does not give you direct access to the cursor these statements create. EGL uses the SQL record variable name to find this cursorID. This is the default option.
nocursor
This option generates a standard WHERE clause. You must also specify either an explicit SQL statement or a record that includes the keyItems property. Validation does not permit you to specify nocursor and usingKeys in the same statement.
usingKeys
This option provides the conditions for an SQL WHERE clause, specifying the fields from SQLRecordVariable. Those fields should match the corresponding columns in the database rows to be deleted.
from resultSetID
You must specify the result set ID from a previous EGL get or open statement if the following conditions are true:
  • You used embedded SQL statements (using the #sql directive) to create the result set.
  • That code retrieved different sets of columns for update than those in the implicit SQL that EGL generated.
If you are not working with the default result set that EGL normally creates for this SQL record variable, specify a result set ID.

Example

The following example shows the delete statement for SQL:

  try
    get dept forUpdate;
    dept.description = "Test Engineers";
    delete dept;
    commit();
  onException(sqlEx SQLException)
    sqlFailure();     
  end 

Compatibility

Table 1. Compatibility considerations for delete
Platform Issue
COBOL generation For best performance in COBOL, always include the from resultSet clause in the delete statement.

Feedback