Part IV: Modifying Data Chapter 10: Updating and Deleting

 

Chapter 10: Updating and Deleting

Maintaining data integrity while modifying existing records is a core responsibility for any database professional. In this chapter, we’ll explore how to:

• Craft safe UPDATE statements with precise WHERE filters

• Delete data responsibly using DELETE and TRUNCATE

• Control transactions with COMMIT, ROLLBACK, and SAVEPOINT

• Leverage backups and test environments to prevent data loss

Mastering these techniques ensures your production workflows are reliable, reversible, and free from unexpected data corruption.

1. Why Safe Data Modification Matters

Uncontrolled UPDATEs or DELETEs can irreversibly alter or remove critical business information. Key risks include:

• Accidentally updating all rows by omitting a WHERE clause

• Deleting entire tables instead of targeted records

• Leaving partial changes due to interrupted operations

• Violating referential integrity and breaking application logic

By applying rigorous safeguards—filters, transactions, and testing—you prevent costly downtime, protect user data, and maintain stakeholder trust.

2. Crafting Safe UPDATE Statements

Updating records typically involves changing one or more columns for rows that meet specific criteria. Follow these steps to keep updates precise and predictable.

2.1 Always Include a WHERE Clause

sql

-- Dangerous: updates every salary
UPDATE employees
SET salary = salary * 1.10;

-- Safe: updates only top performers
UPDATE employees
SET salary = salary * 1.10
WHERE performance_rating = 'A';

• Omitting WHERE affects all rows.

• Including overly broad conditions risks unintended rows.

• Test with SELECT first (see below).

2.2 Preview Changes with SELECT

Before running an UPDATE, mimic its WHERE filter in a SELECT:

sql

SELECT employee_id, salary
FROM employees
WHERE department = 'Sales';

• Validate the exact rows you’ll impact.

• Check current values and ensure the filter logic is correct.

2.3 Use Subqueries and Joins Safely

When conditions depend on other tables, use joins or subqueries:

sql

UPDATE o
SET o.status = 'shipped'
FROM orders o
JOIN shipments s
  ON o.order_id = s.order_id
WHERE s.shipped_date IS NOT NULL;

• Alias tables (o, s) to clarify source and target.

• Include the join condition in the WHERE or FROM clause to avoid cartesian updates.

2.4 Limit the Scope with Batches

For large tables, break updates into chunks to reduce locks:

sql

-- Pseudocode: update in 10k-row batches
WHILE (1=1) LOOP
  WITH cte AS (
    SELECT employee_id
    FROM employees
    WHERE last_review < '2023-01-01'
    LIMIT 10000
  )
  UPDATE employees e
  SET status = 'inactive'
  FROM cte
  WHERE e.employee_id = cte.employee_id;

  EXIT WHEN NOT FOUND;
END LOOP;

• Batching prevents long-running transactions.

• Allows monitoring and mid-stream rollback if necessary.

3. Deleting Records Responsibly

Deleting data is irreversible in most cases. Learn when to use DELETE versus TRUNCATE and how to avoid accidental data loss.

3.1 DELETE vs. TRUNCATE

Feature	DELETE	TRUNCATE
Syntax	DELETE FROM table WHERE …;	TRUNCATE TABLE table;
Row-by-row vs. Page-level	Row-level, logs individual deletes	Page-level, minimal logging
WHERE support	Yes	No
Triggers fired	Yes	No
Transactional	Fully transactional	Varies by database (often transactional)
Referential integrity	Honors FK ON DELETE actions	May bypass FK checks or require cascading

3.2 Deleting Specific Records

sql

DELETE FROM orders
WHERE order_date < '2022-01-01'
  AND status = 'cancelled';

• Always pair DELETE with a narrow WHERE clause.

• Preview with a matching SELECT before deletion.

3.3 Truncating a Table

Use TRUNCATE when you need to clear all rows:

sql

TRUNCATE TABLE temp_events;

• Faster than DELETE for large tables.

• Resets identity counters in many databases.

• Cannot selectively delete—no WHERE clause allowed.

4. Transaction Control: COMMIT, ROLLBACK & SAVEPOINTS

Transactions group multiple operations into an atomic unit. Either all changes succeed, or none take effect.

4.1 Basic Transaction Flow

sql

BEGIN;   -- Start transaction

UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;

COMMIT;  -- Persist changes
-- ROLLBACK;  -- Undo changes instead

• BEGIN opens a new transaction.

• COMMIT confirms all changes.

• ROLLBACK reverts any operations since BEGIN.

4.2 Using SAVEPOINTS

For multi-step processes, create intermediate rollback points:

sql

BEGIN;

INSERT INTO orders (…) VALUES (…);
SAVEPOINT sp1;

UPDATE inventory
SET stock = stock - 1
WHERE product_id = 100;

-- If inventory update fails:
ROLLBACK TO sp1;

COMMIT;

• SAVEPOINT lets you undo parts of a transaction without aborting everything.

• Improves resilience in complex workflows.

4.3 Autocommit and Session Settings

Most clients default to autocommit, applying each statement immediately. For safety:

• Disable autocommit in scripts.

• Explicitly wrap statements in BEGIN/COMMIT.

• Monitor session settings (SHOW autocommit;).

5. Using Backups and Test Environments

Even with safeguards, human error or unexpected bugs can slip through. Mitigate risk with robust backup and staging practices.

5.1 Regular Backups

• Schedule full and incremental backups.

• Test restores periodically to ensure backup integrity.

• Store backups offsite and rotate retention according to policy.

5.2 Test and Staging Environments

• Development: Draft scripts against anonymized or small copied data.

• Staging: Mirror production schema and volume for final validation.

• Maintain separate credentials and network isolation.

5.3 Dry-Run and Audit Logs

• Include a “dry-run” flag in maintenance scripts to print affected row counts without applying changes.

• Log each executed statement and its row count to an audit table for traceability.

sql

-- Pseudocode for dry-run
IF dry_run THEN
  RAISE NOTICE 'Would delete % rows', (SELECT COUNT(*) FROM target WHERE …);
ELSE
  DELETE FROM target WHERE …;
END IF;

6. Production-Ready Workflows

In real-world teams, data changes are managed via:

1. Version Control: Store SQL scripts in Git, tag each schema change.

2. Migration Tools: Use Flyway or Liquibase to apply changes consistently across environments.

3. Code Reviews: Peer-review UPDATE and DELETE scripts for logical errors.

4. Monitoring: Alert on long-running transactions or excessive locks.

5. Rollback Plans: Always have a documented rollback or compensation strategy before applying changes.

7. Summary and Best Practices

• Always filter with precise WHERE clauses on UPDATE and DELETE.

• Preview changes using SELECT and row counts before executing.

• Use transactions—group operations, and employ SAVEPOINTS for partial rollbacks.

• Prefer DELETE for targeted deletions, TRUNCATE for full-table clears.

• Batch large updates to minimize locks and resource spikes.

• Leverage backups and test environments to validate scripts safely.

• Version control and code reviews ensure repeatable, auditable modifications.

By following these guidelines, you’ll maintain data integrity, prevent accidental loss, and build confidence in your production workflows. With Chapter 10’s principles under your belt, you’re now equipped to safely modify data at scale and keep your databases reliable and consistent.