UPDATE with JOIN in MySQL - Step-by-Step Execution

Concept Flow - UPDATE with JOIN

Start UPDATE

↓

Identify tables

↓

Perform JOIN between tables

↓

Find matching rows

↓

Update target table columns

↓

Finish UPDATE

The UPDATE with JOIN process finds matching rows between tables and updates the target table accordingly.

Execution Sample

MySQL

UPDATE employees e
JOIN departments d ON e.dept_id = d.id
SET e.salary = e.salary + 500
WHERE d.name = 'Sales';

This query increases salary by 500 for employees in the Sales department.

Execution Table

Step	Action	Tables Involved	Rows Matched	Update Applied	Result
1	Start UPDATE	employees, departments	N/A	No	Ready to find matches
2	Perform JOIN ON e.dept_id = d.id	employees, departments	Matches found where dept_id equals id	No	Rows matched for update
3	Apply WHERE d.name = 'Sales'	employees, departments	Filtered to employees in Sales dept	No	Filtered rows for update
4	Update e.salary = e.salary + 500	employees	Filtered rows	Yes	Salaries increased by 500
5	Finish UPDATE	employees	N/A	Yes	Update complete

💡 All matching employees in Sales department updated; no more rows to process.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
Matched Rows	None	All employees joined with departments on dept_id	Only employees where department name is 'Sales'	Same filtered employees with salary increased	Updated employees in Sales department

Key Moments - 3 Insights

Why do we need the JOIN in the UPDATE?

The JOIN connects employees to their departments so we can update only those in the Sales department, as shown in execution_table step 2 and 3.

What happens if the WHERE clause is missing?

Without WHERE, all employees matching the JOIN condition would have their salary increased, not just Sales, as seen in execution_table step 3 filtering.

Does the UPDATE change rows in both tables?

No, only the target table (employees) is updated, even though the JOIN uses departments to filter rows, as shown in execution_table step 4.

Visual Quiz - 3 Questions

Look at the execution table, at which step are the rows filtered to only Sales department employees?

A Step 2

B Step 4

C Step 3

D Step 5

💡 Hint

Check the 'Action' and 'Rows Matched' columns in execution_table row for step 3.

According to variable_tracker, what does 'Matched Rows' contain after step 4?

A All employees joined with departments

B Employees in Sales with increased salary

C Departments only

D No rows matched

💡 Hint

Look at the 'After Step 4' column in variable_tracker for 'Matched Rows'.

If the WHERE clause was removed, what would change in the execution_table?

A Step 4 would update all joined employees

B Step 2 would not perform JOIN

C Step 3 would filter more rows

D Step 5 would not finish

💡 Hint

Refer to key_moments explanation about WHERE clause effect and execution_table step 3 and 4.

Concept Snapshot

UPDATE with JOIN syntax:
UPDATE target_table alias
JOIN other_table alias ON join_condition
SET target_table.column = value
WHERE filter_condition;

Use JOIN to match rows from another table.
WHERE filters which rows to update.
Only target_table rows are changed.

Full Transcript

This visual execution shows how an UPDATE with JOIN works in MySQL. First, the UPDATE starts with the target and joined tables. Then, a JOIN finds matching rows based on a condition. Next, a WHERE clause filters these matches to only those we want to update. After filtering, the UPDATE changes the target table's columns. Finally, the update finishes. Variables track which rows are matched and updated. Key moments clarify why JOIN and WHERE are needed and that only the target table is updated. The quiz tests understanding of filtering steps, variable states, and effects of removing WHERE.

Practice

1. What is the main purpose of using UPDATE with JOIN in MySQL?

easy

A. To select data from multiple tables without updating

B. To delete rows from two tables at once

C. To update rows in one table based on matching rows in another table

D. To create a new table by joining two tables

Solution

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

   UPDATE with JOIN is used to change data in one table using related data from another table by matching rows.

2. Step 2: Compare options

   Only To update rows in one table based on matching rows in another table describes updating one table based on another, which matches the purpose.

3. Final Answer:

   To update rows in one table based on matching rows in another table -> Option C

4. Quick Check:

   UPDATE with JOIN = update one table using another [OK]

Hint: UPDATE with JOIN updates one table using another table's data [OK]

Common Mistakes:

• Confusing UPDATE with JOIN as a SELECT operation
• Thinking UPDATE with JOIN deletes data
• Believing it creates new tables

2. Which of the following is the correct syntax to update table1 using table2 with a JOIN on id in MySQL?

easy

A. UPDATE table1 JOIN table2 ON table1.id = table2.id SET table1.value = table2.value;

B. UPDATE table1 SET table1.value = table2.value JOIN table2 ON table1.id = table2.id;

C. UPDATE table1 SET value = table2.value WHERE table1.id = table2.id JOIN table2;

D. UPDATE table1, table2 SET table1.value = table2.value WHERE table1.id = table2.id;

Solution

1. Step 1: Recall correct UPDATE with JOIN syntax

   The correct syntax places JOIN after UPDATE, then ON condition, then SET clause.

2. Step 2: Check each option

   UPDATE table1 JOIN table2 ON table1.id = table2.id SET table1.value = table2.value; matches the correct syntax: UPDATE table1 JOIN table2 ON ... SET ...; others misuse JOIN placement or syntax.

3. Final Answer:

   UPDATE table1 JOIN table2 ON table1.id = table2.id SET table1.value = table2.value; -> Option A

4. Quick Check:

   UPDATE + JOIN + ON + SET order = B [OK]

Hint: JOIN comes right after UPDATE, then ON, then SET [OK]

Common Mistakes:

• Placing JOIN after SET clause
• Using WHERE instead of ON for JOIN condition
• Separating tables with commas instead of JOIN

3. Given these tables:

users(id, name, city_id)
cities(id, city_name)

What will this query do?

UPDATE users u JOIN cities c ON u.city_id = c.id SET u.name = CONCAT(u.name, ' - ', c.city_name) WHERE c.city_name = 'Paris';

medium

A. Delete users who live in Paris

B. Replace all user names with 'Paris'

C. Add a new user with name 'Paris'

D. Append ' - Paris' to the name of users living in Paris

Solution

1. Step 1: Understand the JOIN and SET clauses

   The query joins users with cities on city_id, then updates user names by appending city name for users in Paris.

2. Step 2: Analyze WHERE condition

   Only users with city_name 'Paris' are updated, so their names get ' - Paris' appended.

3. Final Answer:

   Append ' - Paris' to the name of users living in Paris -> Option D

4. Quick Check:

   UPDATE with JOIN + WHERE filters = append city name [OK]

Hint: JOIN matches rows; SET changes only filtered rows [OK]

Common Mistakes:

• Thinking it replaces all names with 'Paris'
• Confusing UPDATE with INSERT
• Assuming it deletes rows

4. Identify the error in this query:

UPDATE orders o JOIN customers c SET o.status = 'shipped' ON o.customer_id = c.id WHERE c.region = 'West';

medium

A. The ON clause is after SET instead of after JOIN

B. Missing WHERE clause

C. Using JOIN instead of INNER JOIN

D. No error, query is correct

Solution

1. Step 1: Check JOIN syntax order

   ON clause must come immediately after JOIN, before SET clause.

2. Step 2: Locate ON clause in query

   ON is incorrectly placed after SET, causing syntax error.

3. Final Answer:

   The ON clause is after SET instead of after JOIN -> Option A

4. Quick Check:

   ON must follow JOIN, not SET [OK]

Hint: ON clause always follows JOIN, never SET [OK]

Common Mistakes:

• Placing ON after SET clause
• Confusing WHERE and ON clauses
• Assuming JOIN needs INNER keyword

5. You have two tables:

products(id, price, category_id)
categories(id, discount_percent)

You want to reduce the price of all products by their category discount. Which query correctly updates products.price applying the discount using UPDATE with JOIN?

hard

A. UPDATE products p, categories c SET p.price = p.price - (p.price * c.discount_percent / 100) WHERE p.category_id = c.id;

B. UPDATE products p JOIN categories c ON p.category_id = c.id SET p.price = p.price - (p.price * c.discount_percent / 100);

C. UPDATE products p SET p.price = p.price - (p.price * c.discount_percent / 100) JOIN categories c ON p.category_id = c.id;

D. UPDATE products p INNER JOIN categories c WHERE p.category_id = c.id SET p.price = p.price * c.discount_percent;

Solution

1. Step 1: Write correct UPDATE with JOIN syntax

   JOIN must come after UPDATE, then ON condition, then SET clause with calculation.

2. Step 2: Verify discount calculation

   Price is reduced by price * discount_percent / 100, which is correct formula.

3. Step 3: Check other options

   Other options misuse JOIN placement, use comma syntax instead of explicit JOIN, or have incorrect syntax/order and discount formulas.

4. Final Answer:

   UPDATE products p JOIN categories c ON p.category_id = c.id SET p.price = p.price - (p.price * c.discount_percent / 100); -> Option B

5. Quick Check:

   UPDATE + JOIN + ON + SET with correct formula = C [OK]

Hint: JOIN after UPDATE, ON before SET, formula subtracts discount [OK]

Common Mistakes:

• Placing JOIN after SET clause
• Using WHERE instead of ON for JOIN
• Incorrect discount formula