DELETE and Safe Data Modification

Concept

DELETE removes whole rows. Like UPDATE, its WHERE clause is optional and its effect is immediate. Unlike UPDATE, there is no old value left to inspect afterwards.

Syntax

DELETE FROM table_name WHERE condition;

Example

-- Step 1: see exactly what you are about to destroy
SELECT COUNT(*) FROM orders
WHERE  status = 'cancelled' AND order_date < '2023-01-01';

-- Step 2: the same predicate, now as a delete
DELETE FROM orders
WHERE  status = 'cancelled' AND order_date < '2023-01-01';

-- Delete driven by another table (MySQL / MariaDB)
DELETE o
FROM   orders o
JOIN   customers c ON c.id = o.customer_id
WHERE  c.country = 'UK';

-- The portable form
DELETE FROM orders
WHERE  customer_id IN (SELECT id FROM customers WHERE country = 'UK');

Explanation

The two step pattern - identical WHERE, first as a SELECT COUNT(*), then as the DELETE - is the single most useful habit in this entire path. If the count is 3 and you expected 3, proceed. If it is 40,000, you just avoided an incident.

Soft delete

-- Instead of removing the row, mark it
ALTER TABLE orders ADD COLUMN deleted_at DATETIME NULL;

UPDATE orders SET deleted_at = NOW() WHERE id = 1002;

-- Every read then filters the flag out
SELECT * FROM orders WHERE deleted_at IS NULL;

Foreign keys decide what happens next

Deleting a parent row with children is governed by the foreign key's ON DELETE action:

Important rules

• DELETE without WHERE empties the table, row by row, and fires every trigger.
• Deletes are logged and can be rolled back inside a transaction, provided the engine is transactional (InnoDB, not MyISAM).
• A large delete can hold locks for a long time. Delete in batches with LIMIT or a key range on a big table.
• Deleting the parent of a CASCADE relationship can remove far more than the row you named.

Common mistakes

• Typing DELETE FROM orders and pressing enter before the WHERE clause is written.
• Assuming a delete can be undone. Without a transaction or a backup, it cannot.
• Deleting a parent row and being surprised when a cascade removes thousands of children.
• Hard deleting business records that accounting, support or the law will later need.

Best practices

• Count first, delete second, using the identical WHERE clause.
• Wrap it: START TRANSACTION; DELETE ...; -- verify -- COMMIT;
• Batch large deletes: DELETE FROM logs WHERE created_at < '2023-01-01' LIMIT 5000; in a loop keeps locks short.
• Prefer soft deletes for anything a human might want back. This notes application uses a deleted_at column for exactly that reason.
• Give application accounts DELETE privilege only on the tables that genuinely need it.

Practice

1. Write the count-then-delete pair for remove every order item belonging to cancelled orders.
2. Convert a hard delete of an employee into a soft delete, and write the SELECT that ignores soft deleted rows.
3. In the sample schema, what stops you deleting department 10 directly, and what are your two options?