How do you update a table based on another table? | Interview Question

Q: How do you update a table based on another table?

**Target Audience:** Developers who can write a simple UPDATE and now need to drive it from another table, such as a staging import; beginners who want the engine differences laid out. **Difficulty:** Medium **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. MySQL and SQL Server syntax is cited from their manuals and was not run here. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and error message below was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First A shop keeps its price tags on the shelves. One morning the supplier emails a sheet: *Kettle now 27, Blender now 85, Grill now 89, and something called Z-999 at 10.* The shop assistant walks the shelves with the sheet.

The problem

Question presented to candidate: "A supplier sends a file of new prices, which we load into a price_updates table keyed by SKU. Write the statement that updates products.price from it. Then tell me what happens to products that are not in the file, and what happens if the file lists the same SKU twice."

What a strong answer should cover:

The portable form: SET col = (correlated subquery) together with WHERE EXISTS (same match), so unmatched rows are left alone
The join forms per engine: UPDATE ... FROM (PostgreSQL, SQLite 3.33+, and SQL Server with its own variant), UPDATE t JOIN s ON ... SET (MySQL), and MERGE (PostgreSQL 15+, SQL Server, Oracle)
Without the WHERE EXISTS guard the correlated form sets every unmatched row to NULL
Duplicate source rows: UPDATE ... FROM silently picks an arbitrary match, MERGE raises an error in PostgreSQL; de-duplicate the source first (ROW_NUMBER or DISTINCT ON)
Safety: run it in a transaction, check the affected row count or RETURNING, and skip no-op updates with IS DISTINCT FROM

Clarifying questions expected:

Which database and version? The syntax differs more here than almost anywhere else in SQL
Can a SKU appear more than once in the file, and if so which row wins?
Should SKUs missing from products be inserted (an upsert) or ignored?

Code / implementation expected: Yes — one portable UPDATE and one join-style UPDATE, with the result checked by a SELECT.

Schema used by the examples:

update-join

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Developers who can write a simple UPDATE and now need to drive it from another table, such as a staging import; beginners who want the engine differences laid out. Difficulty: Medium Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. MySQL and SQL Server syntax is cited from their manuals and was not run here.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result and error message below was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

A shop keeps its price tags on the shelves. One morning the supplier emails a sheet: *Kettle now 27, Blender now 85, Gri

The solution is hidden so you can try first

15 min read · 12 sections

Run the code

Edit the query and run it against the tables for this question.

SQLUPDATE ... FROM (PostgreSQL, and SQLite since 3.33.0): join the target to the source and copy columns

SQLPortable form: correlated subquery in SET, guarded by WHERE EXISTS so unmatched rows are untouched

SQLThe classic bug: the same correlated SET without WHERE EXISTS sets every unmatched price to NULL

PostgreSQL: the file lists A-100 twice; DISTINCT ON keeps the latest row, RETURNING shows what changed

INSERT INTO price_updates (sku, new_price, effective)
VALUES ('A-100', 25, '2024-04-01');

UPDATE products AS p
SET price = latest.new_price,
    updated = latest.effective
FROM (
  SELECT DISTINCT ON (sku) sku, new_price, effective
  FROM price_updates
  ORDER BY sku, effective DESC
) AS latest
WHERE latest.sku = p.sku
RETURNING p.id, p.sku, p.price, p.updated;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.