UPDATE, DELETE, Multi-Table DML, and Guardrails Against Wide Changes

Learning outcomes

An INSERT that fails usually announces itself. An UPDATE or DELETE with a predicate that is syntactically valid but too broad can succeed perfectly while changing the wrong rows. Production safety therefore begins before the DML statement: prove the target set, understand how MySQL joins the target to other tables, protect the operation with a transaction or recovery plan, modify, verify, and only then commit.

Prepare a known target set

If you completed Lesson 1, keep the lab. Otherwise run the Lesson 1 setup and seed a small set of work orders:

USE servicehub_write_lab;INSERT INTO work_orders (request_key,customer_id,technician_id,status,priority,labor_minutes,parts_cost) VALUES ('MOD-001',1,NULL,'open',1,0,0.00), ('MOD-002',1,NULL,'open',2,0,0.00), ('MOD-003',2,1,'assigned',2,30,5.00), ('MOD-004',3,2,'assigned',3,45,20.00)ON DUPLICATE KEY UPDATE request_key=request_key;SELECT work_order_id,request_key,customer_id,technician_id,status,priorityFROM work_ordersWHERE request_key LIKE 'MOD-%'ORDER BY work_order_id;

The idempotent-looking seed is only for this disposable lab; production upsert semantics should update intentionally meaningful columns, not merely suppress duplicates.

Qualified UPDATE: preview the same predicate first

Operations wants to assign Ava (technician 1) to unassigned, priority-1 work orders for customer 1. First produce the exact keys:

SELECT work_order_id, request_key, status, priority, technician_idFROM work_ordersWHERE customer_id=1  AND priority=1  AND status='open'  AND technician_id IS NULLORDER BY work_order_id;

Record the count and identifiers. Then perform the UPDATE inside an explicit transaction so you have a verification window before commit:

START TRANSACTION;UPDATE work_ordersSET technician_id=1, status='assigned'WHERE customer_id=1  AND priority=1  AND status='open'  AND technician_id IS NULL;SELECT ROW_COUNT() AS rows_changed;SELECT work_order_id,request_key,technician_id,statusFROM work_ordersWHERE customer_id=1 AND priority=1ORDER BY work_order_id;COMMIT;

MySQL clients commonly report both rows matched and rows changed for UPDATE. ROW_COUNT() normally reports actually changed rows, while connectors using the CLIENT_FOUND_ROWS capability can report matched rows instead. Do not make business correctness depend on a driver-specific interpretation without testing that connector contract.

Failure case: a wide UPDATE that is valid SQL

The dangerous version omits the customer and priority filters:

START TRANSACTION;UPDATE work_ordersSET status='cancelled'WHERE status='open';SELECT ROW_COUNT() AS rows_changed;SELECT work_order_id,request_key,statusFROM work_ordersWHERE status='cancelled'ORDER BY work_order_id;-- The preview reveals the change is too broad, so undo it.ROLLBACK;

This is a useful failure drill because the statement succeeds. The transaction is the recovery mechanism only because the rows are InnoDB and no implicit-commit statement intervenes. A backup or snapshot strategy is still required for large or operationally risky changes where rollback cost, lock duration, or accidental commit must be considered.

Multi-table UPDATE: MySQL-specific join syntax

MySQL can update rows based on joins, and a multiple-table UPDATE can modify more than one named target table. The common safe pattern is to update one target table using joined lookup criteria. Suppose all open work orders for customers in the south region should be raised to priority 1 for an incident.

SELECT w.work_order_id,w.request_key,c.region,w.priority,w.statusFROM work_orders AS wJOIN customers AS c ON c.customer_id=w.customer_idWHERE c.region='south' AND w.status='open'ORDER BY w.work_order_id;START TRANSACTION;UPDATE work_orders AS wJOIN customers AS c ON c.customer_id=w.customer_idSET w.priority=1WHERE c.region='south' AND w.status='open';SELECT ROW_COUNT() AS rows_changed;ROLLBACK;

For MySQL multiple-table UPDATE syntax, ORDER BY and LIMIT are not permitted. Single-table UPDATE supports them, but using LIMIT as a substitute for an accurate business predicate is dangerous: it caps quantity without proving identity, and without deterministic ordering it may select an unintended subset.

DELETE: preserve the evidence needed to prove scope

Deletion removes the row that would otherwise help you diagnose the target set. Preview primary keys first. For production retention jobs, consider bounded key ranges or archival workflows rather than a giant unqualified delete.

SELECT work_order_id,request_key,statusFROM work_ordersWHERE request_key='MOD-002';START TRANSACTION;DELETE FROM work_ordersWHERE request_key='MOD-002';SELECT ROW_COUNT() AS rows_deleted;SELECT COUNT(*) AS should_be_zeroFROM work_orders WHERE request_key='MOD-002';ROLLBACK;SELECT COUNT(*) AS restored_by_rollbackFROM work_orders WHERE request_key='MOD-002';

A multi-table DELETE has its own MySQL syntax (for example, DELETE w FROM work_orders AS w JOIN ...) and also does not support ORDER BY/LIMIT in the multiple-table form. Prefer deleting from one well-qualified target unless there is a clear need to delete from several joined targets atomically.

Index the qualification path, but verify the plan

Guardrails are not only logical. A wide modification can hold locks for a long time if MySQL must scan many rows to discover the target. The Chapter 06 schema includes ix_work_orders_status_priority(status,priority,work_order_id). Use EXPLAIN on the equivalent SELECT to understand access paths before a large change.

EXPLAIN FORMAT=TREESELECT work_order_id,request_keyFROM work_ordersWHERE status='open' AND priority=1ORDER BY work_order_id;

EXPLAIN is optimizer evidence, not a lock-duration guarantee. Actual duration depends on cardinality, concurrent transactions, I/O, buffer state, triggers, foreign keys, and transaction scope.

The production guardrail workflow

What InnoDB is doing during UPDATE and DELETE

An UPDATE first has to locate candidate records. InnoDB locks the index records required by the statement, creates undo information for changed rows, modifies the clustered record, and maintains any secondary indexes whose indexed columns change. A DELETE similarly marks/removes the logical row transactionally; physical cleanup and purge behavior are deeper InnoDB topics covered later. Locks remain until the transaction commits or rolls back.

This is why predicate quality affects concurrency. If a predicate can use a selective index, MySQL can usually identify the target with less scanning than a broad unindexed condition. A scan that examines and locks many records can block unrelated work even if only a small subset is ultimately changed. The exact locks depend on isolation level, access path, uniqueness, and statement form, so verify with plan and lock evidence instead of assuming “row-level locking means exactly one lock per changed row.”

Foreign keys add another layer. A parent/child modification can require integrity checks and may cascade if the schema explicitly defines referential actions. MySQL’s documentation also warns that a multi-table UPDATE involving InnoDB foreign-key relationships can fail if optimizer table processing order conflicts with parent/child dependencies; in such cases, prefer single-table updates and defined ON UPDATE behavior rather than forcing a complicated multi-table change.

Affected-row numbers are therefore an observation, not a complete safety proof. Verify business invariants after DML: unchanged control rows, expected child relationships, nonnegative quantities, and the exact key set intended by the change request.

Production judgment and next step

For small, well-indexed changes, an explicit transaction plus verification can be an excellent guardrail. For huge corrections, holding one transaction open may create its own hazards: long lock retention, undo growth, replica lag, prolonged crash recovery work, and operational uncertainty. Large changes should be designed as resumable, observable batches with a tested recovery strategy.

Lesson 3 formalizes the transaction boundary itself: autocommit, explicit transactions, savepoints, implicit commits, and the important fact that not every SQL error rolls back the whole transaction.

Authoritative references

• MySQL 8.4 Reference Manual — UPDATE Statement
• MySQL 8.4 Reference Manual — DELETE Statement
• MySQL 8.4 Reference Manual — ROW_COUNT()
• MySQL 8.4 Reference Manual — Optimizing InnoDB Transaction Management