Deleting Rows

To delete rows from a table or view use the DELETE statement. Deleting a row from a view deletes the row from the table that contains the row.

You can use the searched form of the DELETE statement or the positioned form with a cursor.

The searched form of the DELETE statement uses a WHERE clause to specify a search condition. Any row which matches the search condition in the WHERE clause is deleted.

The positioned form of the DELETE statement uses the CURRENT OF clause to name a cursor which has previously been declared. Only the row where the cursor is positioned is deleted. Following is an example.

Important! The DELETE is a powerful statement, and can delete all rows of a table if you neglect to specify a WHERE clause that limits the deletion.

 Problem

Delete all rows from the CUSTOMERS table where the STATE column
contains the value specified in the host variable WS-STATE.
 Solution
               .
               .
       (COBOL statements)
               .
               .
  1   EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
  2   EXEC SQL WHENEVER SQLWARNING CONTINUE END-EXEC.
  3   EXEC SQL WHENEVER SQLERROR GO TO ERRORTN END-EXEC.
  4   MOVE 'GU' TO WS-STATE.
  5   EXEC SQL
  6       DELETE
  7           FROM CUSTOMERS
  8           WHERE STATE = :WS-STATE
  9   END-EXEC.

Line 1

The exception condition, NOT FOUND, directs the program to continue execution if an SQL return code of +100 is received.

Line 2

The exception condition, SQLWARNING, directs the program to continue execution if a warning condition or a positive SQL return code other than +100 is received.

Line 3

The exception condition, SQLERROR, directs the program to go to an error handling routine in the host program if a negative SQL return code is received.

Lines 6-8

The DELETE statement deletes all rows where the value of the STATE column equals the value of the host variable WS-STATE.