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.