Lesson 11 — Removing Data

DELETE

DELETE removes rows permanently. Like UPDATE, the WHERE clause is critical. Understanding soft delete vs hard delete is important for production systems.

SQL — Basic DELETE 
-- Delete a specific row:
DELETE FROM learners
WHERE  learner_id = 7;

-- Delete based on a condition:
DELETE FROM learners
WHERE  is_active = FALSE AND enrolment_date < '2022-01-01';

-- WITHOUT WHERE — deletes ALL rows (keeps table structure):
-- DELETE FROM learners;  -- devastating — all data gone!

-- Safe workflow — preview first:
SELECT * FROM learners WHERE learner_id = 7;
-- Looks right? Then:
DELETE FROM learners WHERE learner_id = 7;
SQL — TRUNCATE 
-- TRUNCATE — deletes all rows much faster than DELETE (no row-by-row logging):
TRUNCATE TABLE learners_temp;
-- Cannot be rolled back in MySQL (can in PostgreSQL)
-- Does not fire row-level triggers
-- Resets auto-increment counter

-- TRUNCATE vs DELETE:
-- DELETE: logs each row, fires triggers, can be rolled back, can use WHERE
-- TRUNCATE: minimal logging, faster, no WHERE clause allowed, resets identity
SQL — Soft delete pattern 
-- Soft delete — preferred in production systems:
-- Instead of physically removing the row, mark it as deleted
-- Data is preserved for auditing; relationships remain intact

-- Add an is_deleted column:
ALTER TABLE learners ADD COLUMN is_deleted BOOLEAN DEFAULT FALSE;
ALTER TABLE learners ADD COLUMN deleted_at TIMESTAMP NULL;

-- Soft delete:
UPDATE learners
SET    is_deleted = TRUE,
       deleted_at = CURRENT_TIMESTAMP
WHERE  learner_id = 7;

-- All queries then filter out deleted rows:
SELECT * FROM learners WHERE is_deleted = FALSE;

-- Recover a soft-deleted record:
UPDATE learners
SET    is_deleted = FALSE, deleted_at = NULL
WHERE  learner_id = 7;

Practice Task

Your Turn

Write: (1) DELETE statement to remove a specific learner by ID (with the preview SELECT); (2) DELETE to remove all inactive learners who enrolled before 2020; (3) Add is_deleted and deleted_at columns to a test table, implement soft delete for three learners, and write a query that returns only active records.

Common Mistakes

  • DELETE without WHERE deletes everything.
  • Deleting a parent row with child rows — FK constraint violation unless ON DELETE CASCADE is set.
  • TRUNCATE vs DELETE — TRUNCATE is not always rollback-safe; do not use it in the middle of a transaction you plan to roll back.
  • Hard delete in production systems — once deleted, the data and its audit trail are gone.

Professional Tip

In financial, healthcare, and government systems, hard deletes are often prohibited by regulation. Everything must be auditable. Implement soft delete and train your team to filter by is_deleted = FALSE in all queries.

Mini Quiz

What is the difference between DELETE and TRUNCATE?