UPDATE & Safe State Changes — SQL Course

What you'll learn

Modify rows with precise predicates, computed assignments, joins, returning clauses, and verification-first habits. The lab uses PostgreSQL while identifying the semantics that transfer to other relational systems.

By the end of this lesson, you'll be able to:

Apply UPDATE SET to a realistic data question
Apply Safe predicates to a realistic data question
Apply UPDATE FROM to a realistic data question
Apply Computed updates to a realistic data question

Core mental model

SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.

Concept	What it means	Decision rule
Target predicate	The condition selecting changed rows	Preview the exact predicate with SELECT first
UPDATE FROM	An update joined to another relation	Ensure the join supplies at most one source row per target
Affected rows	The number or values changed	Use RETURNING and assert the expected cardinality

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the bounded update question and the exact grain of the expected result.
Inspect table definitions, keys, constraints, representative values, and row counts.
Write the smallest correct query with explicit columns, aliases, and predicates.
Test missing, duplicate, boundary, and NULL cases before trusting the result.
Inspect the execution plan or affected rows when cost or data change matters.
Save the query with its assumptions, parameters, verification, and recovery notes.

Make results explainable

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Preview and apply a controlled adjustment

The same predicate is reviewed first, then the update returns every changed row.

adjust_prices.sql

SELECT id, sku, price
FROM inventory.products
WHERE active AND sku LIKE 'SQL-%';

UPDATE inventory.products
SET price = round(price * 1.05, 2)
WHERE active AND sku LIKE 'SQL-%'
RETURNING id, sku, price;

Production practice

Contract

Define the expected row grain, inputs, output columns, invariants, and failure or empty-result behavior before writing SQL.

Verification

Use representative fixtures and independent row-count, uniqueness, NULL, and boundary checks; compare plans when cost matters.

Operations

Save reviewed SQL with explicit schema names where appropriate, bounded scope, least privilege, observability, and a recovery path for changes.

Common failure mode

An UPDATE without a WHERE clause is valid SQL and changes every row. Use a transaction and verify affected-row expectations.

Independent workshop

Build a review-ready bounded update lab against the course commerce dataset.

Your finished workshop must include:

UPDATE SET
Safe predicates
UPDATE FROM
Computed updates
RETURNING
Verification notes and edge-case evidence

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

Target predicate: Preview the exact predicate with SELECT first
UPDATE FROM: Ensure the join supplies at most one source row per target
Affected rows: Use RETURNING and assert the expected cardinality

Quick check

1. Which rule best applies to Target predicate?

2. Which rule best applies to UPDATE FROM?

3. Which rule best applies to Affected rows?

Next: DELETE, TRUNCATE & Data-Loss Safety