SQL Querying

INSERT, UPDATE, and DELETE

Data modification statements derive a target row set and apply a change subject to constraints. Safe DML makes columns and predicates explicit, previews multirow effects, checks affected counts, and groups dependent changes inside a deliberate transaction boundary.

INSERT can add explicit rows or the result of a query; an explicit target column list protects against schema-order coupling.
UPDATE changes every target row satisfying its predicate, and DELETE removes every satisfying row.
A missing or broad predicate is a set operation, not a prompt.
Joined multirow writes need one unambiguous source row per target row.
Constraints and triggers can reject or extend a statement; the affected target rows alone may not describe all consequences.
Dependent writes belong in one transaction so callers observe either the complete business change or its rollback.

Derive, verify, then modify

Portable insertion and targeted update

INSERT INTO order_items
  (order_id, line_no, product_id, quantity, unit_price)
VALUES
  (:order_id, 1, :product_id, :quantity, :agreed_price);

UPDATE orders
SET status = 'CANCELLED'
WHERE order_id = :order_id
  AND status = 'PENDING';

Preview the exact multirow delete set

SELECT order_id, customer_id, placed_at, status
FROM orders
WHERE status = 'CANCELLED'
  AND placed_at < :retention_cutoff
ORDER BY order_id;

DELETE FROM orders
WHERE status = 'CANCELLED'
  AND placed_at < :retention_cutoff;

Operational checks around a write
Before	During	After
Select target keys and count them	Use a transaction for dependent statements	Check affected row count
Confirm constraints and cascades	Keep batches bounded to control locks/log growth	Reconcile invariants or audit output
Take the needed backup or recovery point	Handle deadlocks/retries at the transaction boundary	Commit only when the whole change is valid

The status update is a compare-and-set: it succeeds only while the order remains pending. An affected count of zero signals “missing or already transitioned,” not success. Creating an order and its items should be one transaction because either half alone violates the business invariant.