Data Manipulation with SQL › Data Manipulation Operations › Deleting Data

Deleting Data

Using DELETE

You erase rows from a table using a DELETE statement. As with UPDATE, there are two types of DELETE statement:

• If the WHERE clause contains a search condition, the statement deletes any row that meets the search condition—this is a searched delete
• If the DELETE statement specifies WHERE CURRENT OF cursor-name, the statement deletes only the row on which the cursor is positioned—this is a positioned delete

Note: For more information about positioned deletes, see Using a Cursor.

Searched Deletes

The statement in this example deletes all rows from the BENEFITS table for a fiscal year that precedes the one specified in the :FISCAL-YEAR host variable:

EXEC SQL
  DELETE FROM BENEFITS
    WHERE FISCAL_YEAR < :FISCAL-YEAR
END-EXEC.

If no rows satisfy the selection criteria in the WHERE clause of a searched delete, SQLCODE will be set to 100.

Checking DELETE Status

The DBMS disallows an attempt to delete a row from a referenced table in a relationship if a row with a matching foreign key exists in a referencing table.

For example, since a referential constraint has been created between the EMPLOYEE table and the POSITION table (with column EMP_ID in POSITION referencing column EMP_ID in EMPLOYEE), you cannot delete employee 1234 from the EMPLOYEE table if employee 1234 exists in the POSITION table.

To detect a referential constraint violation on a DELETE statement, test for SQLCERC = 1060:

IF SQLCERC = 1060 PERFORM REFERENTIAL-ERROR
ELSE IF SQLCODE < 0 GOTO ERROR-ROUTINE.

Automatic Rollback

If the attempt to delete one row of a searched delete fails:

• Statement execution halts
• The DBMS returns an error value to SQLCODE
• The results of the DELETE statement are automatically rolled back

Important! When you issue a DELETE, be sure that the statement includes a WHERE clause. If the WHERE clause is omitted, CA IDMS deletes all rows from the named table.