UPDATE with subquery preview in SQL - Step-by-Step Execution

Concept Flow - UPDATE with subquery preview

Start UPDATE statement

↓

Evaluate subquery for new values

↓

Match rows to update

↓

Apply new values from subquery

↓

Finish UPDATE

The UPDATE statement runs a subquery to get new values, then updates matching rows with those values.

Execution Sample

SQL

UPDATE employees
SET salary = (
  SELECT AVG(salary) FROM employees WHERE department = 'Sales'
)
WHERE department = 'Sales';

This updates all employees in Sales to have the average Sales salary.

Execution Table

Step	Action	Subquery Result	Rows Matched	Update Applied
1	Start UPDATE	N/A	N/A	N/A
2	Run subquery to get AVG salary for Sales	AVG(salary) = 6000	N/A	N/A
3	Find employees in Sales department	N/A	3 rows matched	N/A
4	Set salary = 6000 for matched rows	N/A	3 rows matched	Salaries updated to 6000
5	Finish UPDATE	N/A	N/A	N/A

💡 All Sales employees updated with average Sales salary, then UPDATE ends.

Variable Tracker

Variable	Start	After Step 2	After Step 4	Final
salary (for Sales employees)	varies (e.g. 5000, 7000, 6000)	6000 (subquery result)	6000	6000

Key Moments - 2 Insights

Why does the subquery run only once before updating all rows?

The subquery calculates the average salary once (see Step 2 in execution_table) and uses that single value to update all matched rows (Step 4). It does not run per row.

What happens if the subquery returns NULL?

If the subquery returns NULL, the salary will be set to NULL for matched rows. This is because the UPDATE uses the subquery result directly (Step 4).

Visual Quiz - 3 Questions

Look at the execution_table, what is the subquery result at Step 2?

A 6000

B NULL

C 3

D 5000

💡 Hint

Check the 'Subquery Result' column at Step 2 in the execution_table.

At which step are the rows matched for update?

A Step 1

B Step 4

C Step 3

D Step 5

💡 Hint

Look at the 'Rows Matched' column in the execution_table.

If the subquery returned NULL, what would happen at Step 4?

A Salaries would be unchanged

B Salaries would be set to NULL

C Error occurs, update stops

D Subquery runs again

💡 Hint

Refer to key_moments about subquery returning NULL and Step 4 update action.

Concept Snapshot

UPDATE with subquery:
- Use UPDATE table SET column = (subquery)
- Subquery runs once, returns value
- Rows matching WHERE updated with that value
- If subquery NULL, column set to NULL
- Efficient way to set values based on query

Full Transcript

This visual trace shows how an UPDATE statement with a subquery works. First, the UPDATE starts. Then the subquery runs once to get the new value (average salary for Sales). Next, rows matching the WHERE condition (Sales department) are found. Then those rows have their salary updated to the subquery result. Finally, the UPDATE finishes. Variables like salary change from their original values to the new average. Key points include that the subquery runs once, not per row, and if it returns NULL, the updated column becomes NULL. The quiz checks understanding of these steps.

Practice

1. What does an UPDATE statement with a subquery do in SQL?

easy

A. It changes values in a table based on data from another table.

B. It deletes rows from a table using a subquery.

C. It creates a new table from a subquery.

D. It selects rows without changing any data.

Solution

1. Step 1: Understand the purpose of UPDATE with subquery

   UPDATE modifies existing rows in a table. When combined with a subquery, it uses data from another table to decide new values.

2. Step 2: Identify the effect of the subquery

   The subquery provides values to update the target table, ensuring related data stays consistent.

3. Final Answer:

   It changes values in a table based on data from another table. -> Option A

4. Quick Check:

   UPDATE with subquery = change data using other table [OK]

Hint: UPDATE with subquery changes data using another table's values [OK]

Common Mistakes:

• Thinking UPDATE deletes rows
• Confusing UPDATE with SELECT
• Believing UPDATE creates new tables

2. Which of the following is the correct syntax for updating a table using a subquery in SQL?

easy

A. UPDATE table1 SET (col1) = (SELECT col2 FROM table2);

B. UPDATE table1 FROM (SELECT col2 FROM table2) SET col1 = col2;

C. UPDATE table1 SET col1 = SELECT col2 FROM table2 WHERE id = table1.id;

D. UPDATE table1 SET col1 = (SELECT col2 FROM table2 WHERE id = table1.id);

Solution

1. Step 1: Review correct UPDATE with subquery syntax

   The correct syntax uses SET column = (SELECT ...), with the subquery in parentheses returning a single value.

2. Step 2: Check each option

   UPDATE table1 SET col1 = (SELECT col2 FROM table2 WHERE id = table1.id); correctly uses SET col1 = (SELECT ... WHERE ...). Options B, C, and D have syntax errors or missing parentheses.

3. Final Answer:

   UPDATE table1 SET col1 = (SELECT col2 FROM table2 WHERE id = table1.id); -> Option D

4. Quick Check:

   SET col = (SELECT ...) is correct syntax [OK]

Hint: Use SET col = (SELECT ...) with parentheses for subquery [OK]

Common Mistakes:

• Omitting parentheses around subquery
• Placing SELECT outside SET
• Using FROM incorrectly in UPDATE

3. Given these tables:

Employees(emp_id, dept_id, salary)
Departments(dept_id, bonus)

What will this query do?

UPDATE Employees SET salary = salary + (SELECT bonus FROM Departments WHERE Departments.dept_id = Employees.dept_id);

medium

A. Delete employees whose salary is less than bonus.

B. Increase each employee's salary by their department's bonus amount.

C. Cause a syntax error due to subquery in UPDATE.

D. Set all salaries to the bonus amount of the first department.

Solution

1. Step 1: Understand the subquery's role

   The subquery selects the bonus from Departments matching the employee's dept_id, returning one value per employee.

2. Step 2: Analyze the UPDATE effect

   Each employee's salary is increased by their department's bonus, as the subquery returns the correct bonus per row.

3. Final Answer:

   Increase each employee's salary by their department's bonus amount. -> Option B

4. Quick Check:

   UPDATE with correlated subquery adds bonus per employee [OK]

Hint: Subquery returns one value per row to update correctly [OK]

Common Mistakes:

• Assuming subquery returns multiple rows causing error
• Thinking it sets salary to bonus instead of adding
• Confusing UPDATE with DELETE

4. Identify the error in this SQL statement:

UPDATE Products SET price = (SELECT discount FROM Discounts WHERE product_id = Products.id);

Assuming Discounts has multiple rows per product_id.

medium

A. Subquery returns multiple rows causing an error.

B. Missing WHERE clause in UPDATE.

C. Incorrect table alias usage.

D. UPDATE cannot use subqueries.

Solution

1. Step 1: Check subquery result for each row

   The subquery selects discount from Discounts where product_id matches. If multiple discounts exist, it returns multiple rows.

2. Step 2: Understand UPDATE with subquery requirement

   UPDATE expects the subquery to return a single value per row. Multiple rows cause an error.

3. Final Answer:

   Subquery returns multiple rows causing an error. -> Option A

4. Quick Check:

   Subquery must return one value per row [OK]

Hint: Subquery must return single value per row to avoid error [OK]

Common Mistakes:

• Ignoring multiple rows returned by subquery
• Assuming missing WHERE causes error here
• Believing UPDATE disallows subqueries

5. You want to update a Users table to set each user's status to 'VIP' if their total orders amount exceeds 1000. The Orders table has user_id and amount. Which query correctly uses UPDATE with a subquery to do this?

hard

A. UPDATE Users SET status = 'VIP' WHERE (SELECT amount FROM Orders WHERE user_id = Users.id) > 1000;

B. UPDATE Users SET status = 'VIP' WHERE EXISTS (SELECT 1 FROM Orders WHERE user_id = Users.id AND amount > 1000);

C. UPDATE Users SET status = 'VIP' WHERE id IN (SELECT user_id FROM Orders GROUP BY user_id HAVING SUM(amount) > 1000);

D. UPDATE Users SET status = (SELECT 'VIP' FROM Orders WHERE user_id = Users.id AND amount > 1000);

Solution

1. Step 1: Understand the requirement

   We must update Users whose total order amount exceeds 1000. This requires grouping Orders by user_id and summing amounts.

2. Step 2: Analyze each option

   UPDATE Users SET status = 'VIP' WHERE (SELECT amount FROM Orders WHERE user_id = Users.id) > 1000; uses a subquery in WHERE that returns multiple rows if user has multiple orders, causing an error. UPDATE Users SET status = (SELECT 'VIP' FROM Orders WHERE user_id = Users.id AND amount > 1000); tries to set status from subquery returning 'VIP' per order, which is incorrect. UPDATE Users SET status = 'VIP' WHERE EXISTS (SELECT 1 FROM Orders WHERE user_id = Users.id AND amount > 1000); checks if any order > 1000, not total sum. UPDATE Users SET status = 'VIP' WHERE id IN (SELECT user_id FROM Orders GROUP BY user_id HAVING SUM(amount) > 1000); correctly uses WHERE id IN with grouped subquery filtering users with sum(amount) > 1000.

3. Final Answer:

   UPDATE Users SET status = 'VIP' WHERE id IN (SELECT user_id FROM Orders GROUP BY user_id HAVING SUM(amount) > 1000); -> Option C

4. Quick Check:

   Use GROUP BY and HAVING in subquery for totals [OK]

Hint: Use GROUP BY and HAVING in subquery for conditional UPDATE [OK]

Common Mistakes:

• Using WHERE EXISTS without aggregation
• Setting column from subquery returning multiple rows
• Ignoring grouping for total sums