Chapter 10: Updating and Deleting Data

Databases evolve continuously; records change, become obsolete, or must be removed. The UPDATE, DELETE, and TRUNCATE statements perform these operations, and each carries risk if used without precision. This chapter shows best practices, safe filters, and recovery options when changes go wrong.

UPDATE and DELETE best practices

Updates modify data in place; deletes remove it. Both are straightforward, yet dangerous if filters are missing or too broad. Always specify a clear WHERE clause and verify the expected row count.

Basic UPDATE form

UPDATE employees
SET salary = salary * 1.05
WHERE department = 'Engineering';

Multiple columns can be updated in the same statement:

UPDATE products
SET price = price * 0.9,
    updated_at = CURRENT_TIMESTAMP
WHERE category = 'Clearance';

Basic DELETE form

DELETE FROM sessions
WHERE last_active < CURRENT_DATE - INTERVAL '30 days';

Omitting WHERE removes every row from the table, which is almost never intended. Add safety limits in scripts that generate SQL automatically.

Engine-specific batch updates

• PostgreSQL: supports RETURNING to capture changed rows.
• MySQL: supports LIMIT for partial deletes or updates (non-standard).
• SQL Server: supports OUTPUT to capture old and new values.

-- PostgreSQL
UPDATE accounts
SET balance = balance - 50
WHERE id = 123
RETURNING id, balance;

-- SQL Server
UPDATE dbo.accounts
SET balance = balance - 50
OUTPUT deleted.balance AS old_balance, inserted.balance AS new_balance
WHERE id = 123;

-- MySQL
DELETE FROM logs
WHERE created_at < NOW() - INTERVAL 90 DAY
LIMIT 10000;   -- avoid long locks

Using WHERE, subqueries, and safeguards

The WHERE clause filters which rows to affect. Combine it with joins or subqueries to match related records precisely.

Targeting related rows

-- Reduce salaries for departments that exceeded budget
UPDATE employees
SET salary = salary * 0.95
WHERE department_id IN (
  SELECT id FROM departments WHERE budget_used > budget_limit
);

You can also join directly in engines that support UPDATE ... JOIN or DELETE ... USING syntax.

-- MySQL and SQL Server
UPDATE e
SET e.salary = e.salary * 0.95
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.budget_used > d.budget_limit;

-- PostgreSQL
DELETE FROM orders o
USING customers c
WHERE o.customer_id = c.id
  AND c.status = 'INACTIVE';

Verifying before execution

First preview the affected rows with a SELECT that uses the same filter. When certain, replace SELECT with UPDATE or DELETE.

-- Preview what would be deleted
SELECT id, email FROM users
WHERE email LIKE '%@test.invalid';

-- Then safely delete
DELETE FROM users
WHERE email LIKE '%@test.invalid';

Transactional safeguards

Use transactions to isolate changes and roll back on error.

BEGIN;

UPDATE inventory
SET quantity = quantity - 1
WHERE product_id = 1001;

DELETE FROM carts
WHERE product_id = 1001
  AND user_id = 42;

COMMIT;

Using conditions from other tables

You can base updates or deletes on computed or aggregated values from related tables.

-- Deactivate customers with no orders in the past year
UPDATE customers
SET active = FALSE
WHERE id NOT IN (
  SELECT DISTINCT customer_id FROM orders
  WHERE order_date > CURRENT_DATE - INTERVAL '1 year'
);

UPDATE customers c
SET active = FALSE
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
    AND o.order_date > CURRENT_DATE - INTERVAL '1 year'
);

TRUNCATE and restoring from backups

TRUNCATE quickly removes all rows from a table while keeping its structure. It typically resets identity counters and uses less logging than DELETE.

TRUNCATE TABLE logs;

Alternatives for safe clearing

-- Use DELETE with WHERE to preserve control
DELETE FROM logs
WHERE created_at < CURRENT_DATE - INTERVAL '90 days';

To reclaim storage space after mass deletions, run engine-specific maintenance such as VACUUM (PostgreSQL, SQLite) or OPTIMIZE TABLE (MySQL).

Restoring deleted or truncated data

Accidental data removal happens. Your ability to recover depends on logging and backup strategies.

• Point-in-time recovery: use write-ahead logs or transaction logs to restore up to the moment before the error.
• Regular full backups: restore the latest backup and replay logs if possible.
• Logical dumps: maintain daily pg_dump, mysqldump, or BACKUP DATABASE outputs for selective restore.

-- PostgreSQL example recovery
pg_restore --dbname=production --table=users backup.dump

-- MySQL
mysql -u root -p production < backup.sql

-- SQL Server
RESTORE DATABASE Production
FROM DISK = 'D:\backups\prod_full.bak'
WITH REPLACE, RECOVERY;

By combining disciplined WHERE filters, short transactions, and reliable backups, you can manage data changes confidently and avoid irreversible mistakes.