0Pricing
SQL Academy · Lesson

UPDATE with FROM and JOIN-style Updates

Update rows using values from another table with UPDATE ... FROM (PostgreSQL) and the standard MERGE statement.

UPDATE with FROM and JOIN-style Updates is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Basic UPDATE

Change values in matching rows:

UPDATE users SET full_name = 'Alice Adams' WHERE id = 1;

Multiple Columns

Comma-separated SET list:

UPDATE users
SET full_name = 'Alice', last_login = NOW(), visits = visits + 1
WHERE id = 1;

Updating Many Rows

Just match more rows with WHERE:

UPDATE orders SET status = 'archived'
WHERE created_at < NOW() - INTERVAL '5 years';

UPDATE with Subquery

Compute the new value from another query:

UPDATE products
SET review_count = (
  SELECT COUNT(*) FROM reviews WHERE reviews.product_id = products.id
);

UPDATE … FROM (PostgreSQL)

JOIN-style update — pull values from another table:

UPDATE products p
SET stock = i.qty
FROM inventory i
WHERE i.product_id = p.id;

UPDATE … FROM with Multiple Tables

The FROM list can include several tables:

UPDATE invoices inv
SET tax_rate = r.rate
FROM users u
JOIN rates r ON r.country = u.country
WHERE inv.user_id = u.id;

Standard SQL: UPDATE … FROM via Correlated Subquery

Portable equivalent (slower):

UPDATE products p
SET stock = (SELECT qty FROM inventory i WHERE i.product_id = p.id);

MERGE for INSERT/UPDATE/DELETE

PostgreSQL 15+ supports MERGE for combined operations — see UPSERT lesson.

RETURNING from UPDATE

Get back the updated rows in one round-trip:

UPDATE orders SET status = 'shipped', shipped_at = NOW()
WHERE id = $1
RETURNING id, status, shipped_at;

Updating with CASE

Conditional new values:

UPDATE orders
SET status = CASE
  WHEN paid_at IS NOT NULL THEN 'paid'
  WHEN created_at < NOW() - INTERVAL '24h' THEN 'expired'
  ELSE status
END;

Watch Out for Missing WHERE

UPDATE users SET active = false; updates EVERY row. In production:

  • Run a SELECT first to verify the row count
  • Wrap in a transaction and check before COMMIT
  • Use BEGIN; ...; SELECT count(*) FROM users WHERE active = false; ROLLBACK; as a dry-run

Update Performance

An UPDATE creates a new row version and marks the old one dead (MVCC). Tables with frequent updates can bloat — covered in VACUUM/MVCC lesson.

Recap

UPDATE … FROM is the JOIN-style update that makes bulk fixes clean.

  • WHERE always — never forget
  • Use RETURNING to verify
  • Test in a transaction with ROLLBACK

Quick Check

Which UPDATE syntax lets you reference another table in PostgreSQL?

Frequently asked questions

Is the “UPDATE with FROM and JOIN-style Updates” lesson free?

Yes — the full text of “UPDATE with FROM and JOIN-style Updates” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “UPDATE with FROM and JOIN-style Updates”?

Update rows using values from another table with UPDATE ... FROM (PostgreSQL) and the standard MERGE statement. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “UPDATE with FROM and JOIN-style Updates” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. INSERT with Multiple Rows
  2. UPSERT: ON CONFLICT DO UPDATE (PostgreSQL)
  3. UPDATE with FROM and JOIN-style Updates
  4. DELETE with USING and Safe Patterns
← Back to SQL Academy