SQL DELETE Statement: Record Deletion & Soft-Delete Patterns

The DELETE statement removes one or more existing rows from a table based on a filter condition. As a DML operation, DELETE can be rolled back inside a database transaction, fires row-level triggers, and records every removed row in the database undo log.

Standard DELETE Syntax

DELETE FROM table_name
WHERE filter_condition;

-- Delete specific student by primary key
DELETE FROM students
WHERE student_id = 5;

-- Delete inactive students enrolled prior to 2020
DELETE FROM students
WHERE is_active = FALSE AND enrollment_date < '2020-01-01';

The Industry "Soft Delete" Pattern

In production financial, healthcare, and enterprise software, hard-deleting records is strictly prohibited due to legal audit compliance. Instead, engineers implement Soft Deletes:

-- Add a soft-delete column
ALTER TABLE students ADD COLUMN is_deleted BOOLEAN DEFAULT FALSE;

-- Instead of DELETE, run an UPDATE:
UPDATE students
SET is_deleted = TRUE
WHERE student_id = 3;

-- Application queries simply filter active records:
SELECT * FROM students WHERE is_deleted = FALSE;

Congratulations! You have completed Module 3 (Data Manipulation). Now let us advance to Module 4 and learn pattern matching starting with SQL LIKE & Wildcards!