DELETE

The DELETE statement removes rows from a table. Like UPDATE, it is controlled by the WHERE clause - and without WHERE it removes every row.

DELETE syntax

• Basic shape: DELETE FROM table WHERE condition.
• DELETE removes whole rows. To clear single columns, use UPDATE and set them to NULL.
• Deleted rows can be recovered only from a backup or image copy. There is no undo after COMMIT.
• Always check SQLCODE after DELETE, then COMMIT to make the removal permanent.

DELETE vs DROP vs TRUNCATE thinking

• DELETE removes rows but keeps the table structure. DROP removes the table itself.
• DELETE without WHERE empties the table row by row and can be rolled back before COMMIT.
• DELETE fires no warnings on large row counts. Count with SELECT first when deleting many rows.
• Child rows with a RESTRICT rule block the DELETE of their parent row (SQLCODE -532).

Example

• Deleting one employee row safely:-