UPDATE

Learning Focus

Use this lesson to understand UPDATE with practical syntax and examples.

Concept Overview

Definition

UPDATE changes values in existing rows. It is the core SQL operation for fixing data entry errors, advancing workflow states (for example, pending to paid), backfilling newly added columns, and maintaining derived fields.

PostgreSQL adds features that make updates safer and easier to verify:

• RETURNING to see exactly which rows changed (and what they changed to)
• UPDATE ... FROM to update based on another table or a derived query
• expressive SET values (arithmetic, functions, CASE, JSON operators)

Why is it important?

• Correctness: updates keep records accurate over time
• Workflows: most applications rely on state transitions and counters
• Safety: a wrong WHERE clause can update far more rows than intended
• Performance: large updates create locks, WAL volume, and table/index bloat if you are not careful

Where does it fit?

UPDATE is part of DML (Data Manipulation Language), along with SELECT, INSERT, and DELETE. In CRUD terms, it is the Update operation.

---

Syntax & Rules

Core Syntax

UPDATE table_name
SET column1 = expression1,
    column2 = expression2,
    ...
WHERE condition
RETURNING returning_expressions;

Available Options / Parameters

Part	What it does	Notes (PostgreSQL)
SET	assigns new values	expressions allowed (CASE, arithmetic, function calls)
WHERE	selects which rows update	omit only if you truly intend to update every row
FROM	joins additional sources	use UPDATE ... FROM to update based on other tables
RETURNING	returns updated rows	best way to verify updates and capture IDs/values
WITH (CTE)	stages rows for update	useful for batching, deduping, and complex selection

Key Rules and Considerations

• Preview the target set with a SELECT using the same WHERE (and joins) before running the update.
• Keep WHERE predicates selective and index-friendly when updating large tables.
• When using UPDATE ... FROM, ensure the join matches at most one source row per target row (or dedupe first).
• Use a transaction (BEGIN/ROLLBACK) for risky or unfamiliar updates.
• Large updates can create bloat; batching and autovacuum awareness matter in production.

---

Step-by-Step Examples

Example 1: Update One Row + Verify with RETURNING (Beginner)

-- Create a sample table
CREATE TABLE employees (
  employee_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name   text NOT NULL,
  department  text NOT NULL,
  salary      numeric(12,2) NOT NULL CHECK (salary >= 0),
  active      boolean NOT NULL DEFAULT true
);

INSERT INTO employees (full_name, department, salary)
VALUES
  ('Ana Ng', 'IT', 68000.00),
  ('Ben Ito', 'Marketing', 72000.00),
  ('Cora Li', 'IT', 91000.00);

-- Preview the row you intend to change
SELECT employee_id, full_name, salary
FROM employees
WHERE full_name = 'Ben Ito';

-- Update + verify the new value
UPDATE employees
SET salary = salary * 1.05
WHERE full_name = 'Ben Ito'
RETURNING employee_id, full_name, salary;

Expected output (preview):

 employee_id | full_name | salary
-------------+-----------+----------
 2           | Ben Ito   | 72000.00
(1 row)

Expected output (RETURNING):

 employee_id | full_name | salary
-------------+-----------+----------
 2           | Ben Ito   | 75600.00
(1 row)

---

Example 2: Update Multiple Columns with a Rule (Intermediate)

-- Give IT employees a new department label and a one-time adjustment.
-- Use CASE to keep the rule explicit.

UPDATE employees
SET department = 'Engineering',
    salary = CASE
      WHEN salary < 80000 THEN salary + 5000
      ELSE salary + 2500
    END
WHERE department = 'IT'
RETURNING employee_id, full_name, department, salary
ORDER BY employee_id;

Expected output:

 employee_id | full_name | department  | salary
-------------+-----------+-------------+----------
 1           | Ana Ng    | Engineering | 73000.00
 3           | Cora Li   | Engineering | 93500.00
(2 rows)

---

Example 3: Update from Another Table with UPDATE ... FROM (Advanced)

-- Orders and payments
CREATE TABLE orders (
  order_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  bigint NOT NULL,
  status       text NOT NULL DEFAULT 'pending',
  paid_at      timestamptz
);

CREATE TABLE payments (
  payment_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id     bigint NOT NULL,
  captured_at  timestamptz NOT NULL
);

INSERT INTO orders (customer_id, status) VALUES
  (10, 'pending'),
  (11, 'pending'),
  (12, 'pending');

INSERT INTO payments (order_id, captured_at) VALUES
  (1, '2026-03-01 09:15:00+00'),
  (3, '2026-03-01 10:05:00+00');

-- Preview which orders have a captured payment
SELECT o.order_id, o.status, p.captured_at
FROM orders o
JOIN payments p ON p.order_id = o.order_id
ORDER BY o.order_id;

-- Update orders based on payments
UPDATE orders o
SET status = 'paid',
    paid_at = p.captured_at
FROM payments p
WHERE p.order_id = o.order_id
  AND o.status <> 'paid'
RETURNING o.order_id, o.status, o.paid_at
ORDER BY o.order_id;

Expected output (preview):

 order_id | status  | captured_at
----------+---------+------------------------
 1        | pending | 2026-03-01 09:15:00+00
 3        | pending | 2026-03-01 10:05:00+00
(2 rows)

Expected output (RETURNING):

 order_id | status | paid_at
----------+--------+------------------------
 1        | paid   | 2026-03-01 09:15:00+00
 3        | paid   | 2026-03-01 10:05:00+00
(2 rows)

---

Practical Use Cases

1) User profile edits with verification

UPDATE users
SET display_name = 'A. Nguyen'
WHERE user_id = 42
RETURNING user_id, display_name, updated_at;

2) Inventory decrements (simple reservation)

UPDATE products
SET stock_on_hand = stock_on_hand - 1
WHERE product_id = 1001
  AND stock_on_hand > 0
RETURNING product_id, stock_on_hand;

3) Backfilling a new column from a source table

UPDATE customer_profiles p
SET country_code = c.country_code
FROM customers c
WHERE c.customer_id = p.customer_id
  AND p.country_code IS NULL
RETURNING p.customer_id, p.country_code;

4) Soft delete (audit-friendly)

UPDATE api_keys
SET revoked_at = now()
WHERE key_id = 900
  AND revoked_at IS NULL
RETURNING key_id, revoked_at;

5) Data hygiene corrections after import

UPDATE leads
SET email = lower(email)
WHERE email ~* '[A-Z]'
RETURNING lead_id, email;

---

Common Mistakes & Troubleshooting

1) Missing WHERE updates every row

Wrong SQL:

UPDATE employees
SET active = false;

Bad outcome:

• Every employee becomes inactive.

Fix:

UPDATE employees
SET active = false
WHERE employee_id = 42
RETURNING employee_id, full_name, active;

---

2) Update affects 0 rows (unexpectedly)

Wrong SQL:

UPDATE employees
SET salary = salary * 1.10
WHERE department = 'IT ';

Bad outcome:

• The query succeeds but changes no rows.

Fix:

SELECT DISTINCT department FROM employees ORDER BY department;

UPDATE employees
SET salary = salary * 1.10
WHERE department = 'IT'
RETURNING employee_id, full_name, salary;

---

3) Violating constraints during UPDATE

Wrong SQL:

UPDATE employees
SET salary = -100;

Typical error:

ERROR:  new row for relation "employees" violates check constraint "employees_salary_check"
DETAIL:  Failing row contains (..., -100.00, ...).

Fix:

UPDATE employees
SET salary = 0
WHERE salary < 0
RETURNING employee_id, full_name, salary;

---

4) UPDATE ... FROM join matches multiple source rows

Wrong SQL:

UPDATE orders o
SET paid_at = p.captured_at
FROM payments p
WHERE p.order_id = o.order_id;

Bad outcome:

• If payments has multiple rows per order, the chosen value can be unpredictable.

Fix (dedupe first):

WITH latest_payment AS (
  SELECT DISTINCT ON (order_id)
    order_id,
    captured_at
  FROM payments
  ORDER BY order_id, captured_at DESC
)
UPDATE orders o
SET paid_at = lp.captured_at
FROM latest_payment lp
WHERE lp.order_id = o.order_id
RETURNING o.order_id, o.paid_at;

---

Debugging checklist:

1. Start a transaction (BEGIN;) so you can ROLLBACK;.
2. Run a preview SELECT with the same filters and joins.
3. Add RETURNING and sanity-check the changed rows.
4. If you hit errors, inspect constraints/triggers and check data types/casts.
5. Use EXPLAIN on the preview SELECT if performance is a concern.

---

Best Practices

• ✅ Always preview the target set before updating. ❌ Avoid running updates based on assumptions about the data.
• ✅ Use RETURNING to verify changes and capture IDs/values. ❌ Avoid relying on “it ran” as proof that it did the right thing.
• ✅ Batch large updates to reduce lock time and WAL spikes. ❌ Avoid single huge updates on hot tables during peak traffic.
• ✅ Keep WHERE predicates index-friendly on large tables. ❌ Avoid scanning a large table just to update a small subset.
• ✅ Treat UPDATE ... FROM as a join that must be deterministic. ❌ Avoid joining to sources that can match multiple rows per target.

---

Hands-On Practice

Use this setup for the exercises:

CREATE TABLE practice_accounts (
  account_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      text NOT NULL UNIQUE,
  plan       text NOT NULL DEFAULT 'free',
  credits    integer NOT NULL DEFAULT 0 CHECK (credits >= 0),
  updated_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO practice_accounts (email, plan, credits)
VALUES
  ('a@example.com', 'free', 0),
  ('b@example.com', 'pro',  10),
  ('c@example.com', 'free', 3);

Exercise 1 (Easy): Update one row and verify

Task: Set plan to pro for a@example.com and return the updated row.

-- Your SQL here

Solution:

UPDATE practice_accounts
SET plan = 'pro',
    updated_at = now()
WHERE email = 'a@example.com'
RETURNING account_id, email, plan, credits, updated_at;

---

Exercise 2 (Medium): Update a subset using a condition

Task: Add 5 credits to every free account that currently has fewer than 5 credits. Return the affected rows.

-- Your SQL here

Solution:

UPDATE practice_accounts
SET credits = credits + 5,
    updated_at = now()
WHERE plan = 'free'
  AND credits < 5
RETURNING account_id, email, plan, credits;

---

Exercise 3 (Advanced): Update from a staging table

Task: Create a staging table of credit adjustments and apply them with UPDATE ... FROM. Return the updated rows.

-- Your SQL here

Solution:

CREATE TABLE practice_credit_adjustments (
  email text PRIMARY KEY,
  delta integer NOT NULL
);

INSERT INTO practice_credit_adjustments (email, delta)
VALUES
  ('a@example.com', 2),
  ('c@example.com', 7);

UPDATE practice_accounts a
SET credits = a.credits + s.delta,
    updated_at = now()
FROM practice_credit_adjustments s
WHERE s.email = a.email
RETURNING a.account_id, a.email, a.credits;

---

Connection to Other Concepts

Concept	Why it matters
SELECT	preview targets and validate results after update
WHERE	the safety control that scopes updates
INSERT INTO	common flow: insert rows then update status fields
DELETE	alternative when you must remove rows instead of modifying
Transactions	BEGIN/COMMIT/ROLLBACK prevents irreversible mistakes
Constraints	prevent invalid updates and enforce business rules
Indexes	speed up target row selection (but increase write cost)

---

Visual Learning Diagram

flowchart TD
  A[SELECT + WHERE (Preview)] --> B[UPDATE]
  B --> C[RETURNING (Verify)]
  B --> D[UPDATE ... FROM]
  B --> E[Transactions]
  E --> F[ROLLBACK / COMMIT]
  B --> G[Constraints]
  B --> H[Batching + Performance]
  H --> I[Bloat / Autovacuum]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5
  class A,B,C,D,E,F,G,H,I allNodes
  class B highlight

---

Common Pitfalls

Pitfall	Consequence	Prevention
Updating without verifying the target set	unintended bulk change	preview with SELECT and use RETURNING
Joining a non-unique source in UPDATE ... FROM	nondeterministic data written	dedupe/aggregate the source in a CTE
Repeated bulk updates on large tables	bloat, slower indexes	batch work; monitor autovacuum behavior
Updating by non-indexed predicates	slow scans and long locks	add indexes for operational update predicates
Overloading state columns with ad-hoc strings	inconsistent workflows	constrain values or enforce transitions

---

Quick Reference

UPDATE t SET col = 'x' WHERE id = 1;
UPDATE t SET col = col + 1 WHERE key = 'k' RETURNING id, col;
UPDATE target t SET col = s.col FROM source s WHERE s.id = t.id;
UPDATE t SET col = COALESCE(col, 0) + 10 WHERE col IS NULL RETURNING id, col;

What's Next

• Previous: INSERT INTO - Review the previous lesson to reinforce context.
• Next: DELETE - Continue to the next concept with incremental complexity.
• Module Overview - Return to this module index and choose another related lesson.