Relational Data Framework Reference › SQL Statements › DELETE FROM Statement—Delete a Row

DELETE FROM Statement—Delete a Row

Issue a DELETE FROM statement in any operation to delete a specified row. In a searched operation, DELETE FROM deletes the row or rows that meet the specified criteria on a SELECT clause. In a cursor operation, DELETE FROM deletes the row currently being processed.

Important! If you use the DELETE FROM statement for a table and do not include a WHERE clause, all rows from the table are deleted. Use the DELETE FROM statement with extreme caution.

Keep this information in mind when using the DELETE FROM statement:

• You can use a host variable for tablename.
• If you are invoking a DELETE FROM statement for a cursor operation, the statement must be issued from an AOF rule, an OPS/REXX program, a TSO/E REXX program, or a TSO CLIST. You cannot invoke SQL commands that perform cursor operations from a TSO terminal.

Use this syntax in an AOF rule or an OPS/REXX program:

ADDRESS SQL "DELETE FROM keywords"
{tablename}
[WHERE searchcriteria]
[SYSTEM(ALL|EXT|sysnames)]
[SYSWAIT(seconds)]
[OUTPUT|NOOUTPUT]
[SYSPLEX]

Use this syntax to invoke the statement from a TSO terminal, a TSO/E REXX program, or a TSO CLIST:

OPSQL DELETE FROM {tablename}
[WHERE searchcriteria]

You may specify these operands for the DELETE FROM statement:

tablename

Defines the name of the table from which you want to delete rows.

searchcriteria

(Optional) For a searched operation, the criteria on a WHERE clause can be any valid search criteria.

(Optional) For a cursor operation, the criteria on a WHERE clause must include CURRENT OF cursorname, which causes the function to be performed on the current row being processed in the cursorname operation. See the example that follows.

SYSTEM

(Optional) Performs cross-system SQL operations. Specify one of these values:

ALL

Routes the SQL command to all active MSF-defined systems, including the local system.

EXT

Routes the SQL command to all remote, active MSF-defined systems.

sysnames

Routes the SQL command to the specified systems. You may specify from one to eight system names as the value of sysnames.

For more information, see the chapter “Using the Relational Data Framework” in the User Guide.

SYSWAIT

(Optional) Defines the number of seconds the SQL processor waits for output from a remote system. You may specify a value between 1 and 300 seconds.

Do not specify a value for SYSWAIT if you specify the SYSTEM(ALL), SYSTEM(EXT), or NOOUTPUT keywords.

OUTPUT or NOOUTPUT

(Optional) Indicates whether the command returns output to the external data queue. Specify OUTPUT to have output returned; otherwise, specify NOOUTPUT.

NOOUTPUT is implied when:

• The ADDRESS SQL command is issued in an AOF rule.
• You specify more than one system name as the value of the SYSTEM(sysnames) keyword.
• You specify SYSTEM(ALL) or SYSTEM(EXT).

SYSPLEX

(Optional) Reduces the scope of the SYSTEM(ALL|EXT) operand to MSF connected systems that belong to the same z/OS sysplex as the command issuer. The keyword has no effect on a list of explicit system names.

Examples: DELETE FROM

These examples illustrate the use of the DELETE FROM statement:

• Example 1-Using DELETE FROM in a Searched Operation

  Suppose that you want to delete from the SYSTEMS table all information associated with the CICS1 system. You can do this by invoking the following statement from OPS/REXX:

  ADDRESS SQL
    "DELETE FROM SYSTEMS WHERE NAME = 'CICS1'"
  

• Example 2-Using DELETE FROM in a Cursor Operation

  To delete the current row being processed in a cursor operation named STATDOWN, issue this command from an AOF rule:

  ADDRESS SQL
    "DELETE FROM APPLICATIONS WHERE CURRENT OF STATDOWN"
  

• Example 3-Using DELETE FROM to Empty a Table

  When you want to delete a relational table, you must first delete all of the rows in that table. You do this by invoking a DELETE FROM statement that omits the WHERE clause, as shown here:

  ADDRESS SQL
    "DELETE FROM SYSTEMS"
  

Then, to remove the relational table from the DIV data set, invoke a DROP TABLE statement, which is described in the next section.