A developer wants to sync prices from a staging table: UPDATE products SET price = price_updates.new_price FROM price_updates WHERE products.sku = price_updates.sku. What should they do before running this UPDATE?
ARun SELECT COUNT(*) FROM price_updates to ensure there are rows to update
BRun the equivalent SELECT showing which rows would be affected, to verify matches and spot any duplicate SKUs in the staging table
CRun EXPLAIN on the UPDATE to check query cost
DRun SELECT * FROM products to see current prices
The key risk with UPDATE-JOIN is updating more rows than intended if the join produces duplicate matches for a target row. Running the equivalent SELECT first shows exactly which rows would be affected and what values they would receive. If the SELECT returns the same products.sku more than once, the join conditions need tightening before running the UPDATE. EXPLAIN shows cost but not which rows would change or whether duplicates exist.
Question 2 Multiple Choice
A developer familiar with MySQL writes an UPDATE-JOIN query and then runs it on a PostgreSQL server. The query fails with a syntax error. What is the most likely cause?
APostgreSQL does not support updating rows based on data in other tables
BPostgreSQL requires a FROM clause instead of writing the JOIN directly after UPDATE
CUPDATE-JOIN only works in MySQL because PostgreSQL uses triggers for this pattern
DThe table aliases used in MySQL syntax are not valid in PostgreSQL
MySQL and SQL Server allow the joined table to appear directly after UPDATE: UPDATE t1 JOIN t2 ON ... SET t1.col = t2.col. PostgreSQL uses a different syntax: UPDATE t1 SET col = t2.col FROM t2 WHERE t1.id = t2.id. The logic is identical — joining to filter and supply values — but the syntax diverges. This is a practical portability trap when switching between database systems.
Question 3 True / False
Using UPDATE with a JOIN to synchronize rows from a staging table is functionally equivalent to looping through rows one at a time and running individual UPDATE statements.
TTrue
FFalse
Answer: False
While both produce the same result, a single UPDATE-JOIN is far more efficient. Individual row-by-row updates incur repeated round-trips to the database, transaction overhead for each update, and query parsing costs multiplied by row count. A set-based UPDATE-JOIN lets the database engine handle the full batch in one optimized operation, using its query planner, indexes, and bulk write mechanisms. For thousands of rows, the performance difference is typically orders of magnitude.
Question 4 True / False
When a JOIN in an UPDATE statement matches multiple rows in the joined table to a single row in the target table, the behavior is consistent and predictable across most major relational databases.
TTrue
FFalse
Answer: False
This is one of the most dangerous aspects of UPDATE-JOIN: when duplicates exist in the joined table, the behavior is database-dependent and often undefined. Some databases apply one arbitrary matching row; others raise an error. This is exactly why you should always test the join as a SELECT first — if duplicates appear in the target table's primary key, the update's behavior is unpredictable. The fix is to tighten join conditions or pre-deduplicate the staging table.
Question 5 Short Answer
Why is it valuable to run an UPDATE-JOIN query as a SELECT statement first, before executing the actual update?
Think about your answer, then reveal below.
Model answer: Running the equivalent SELECT reveals exactly which rows would be modified and what new values they would receive. Most importantly, it exposes the case where the join produces multiple matching rows for a single target row — which would result in undefined or unintended behavior in the actual UPDATE. If the SELECT shows duplicate primary keys in the target table's rows, the join conditions must be tightened. The SELECT is a dry run that makes the update's effects visible and auditable before any data is changed.
The risk unique to UPDATE-JOIN is not just performance but correctness — specifically, updating wrong rows or applying wrong values due to unexpected join cardinality. A SELECT costs nothing and can save a difficult rollback or data corruption scenario.