UPDATE with JOIN in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output after this UPDATE with JOIN?

Given two tables, employees and departments, the query updates employee salaries by adding 1000 for those in the 'Sales' department. What will be the salary of employee with id=3 after running this?

MySQL

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

-- employees table before update:
-- id | name    | department_id | salary
-- 1  | Alice   | 1             | 5000
-- 2  | Bob     | 2             | 6000
-- 3  | Charlie | 1             | 5500

-- departments table:
-- id | name
-- 1  | Sales
-- 2  | HR

A 5500

B 6500

C 6000

D SyntaxError

💡 Hint

Check which employees belong to the 'Sales' department and how their salary changes.

The employee with id=3 is in department 1, which is 'Sales'. The query adds 1000 to their salary of 5500, resulting in 6500.

📝 Syntax

intermediate

Which UPDATE with JOIN syntax is correct in MySQL?

Choose the correct syntax to update a table orders by joining with customers to set orders.status to 'shipped' where customers.region is 'West'.

A UPDATE orders SET status = 'shipped' WHERE customer_id IN (SELECT id FROM customers WHERE region = 'West');

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

C UPDATE orders o, customers c SET o.status = 'shipped' WHERE o.customer_id = c.id AND c.region = 'West';

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

💡 Hint

MySQL uses JOIN syntax directly in UPDATE, not FROM.

Option D uses the correct MySQL syntax with JOIN in UPDATE. Option D uses invalid FROM clause. Option D uses comma syntax instead of JOIN. Option D uses subquery, not JOIN.

❓ optimization

advanced

How to optimize this UPDATE with JOIN for large tables?

You have a large products table and categories table. You want to update products.discount to 10 where categories.name is 'Clearance'. The current query is:

UPDATE products p
JOIN categories c ON p.category_id = c.id
SET p.discount = 10
WHERE c.name = 'Clearance';

Which optimization is best to improve performance?

A Add an index on categories.name and products.category_id columns.

B Rewrite the query using a subquery instead of JOIN.

C Remove the WHERE clause to update all products.

D Use a LEFT JOIN instead of JOIN.

💡 Hint

Indexes help speed up JOIN and WHERE conditions.

Adding indexes on the join and filter columns helps MySQL quickly find matching rows, improving update speed. Other options either reduce precision or do not improve performance.

🔧 Debug

advanced

Why does this UPDATE with JOIN fail with 'Unknown column' error?

Consider this query:

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

It raises an error: Unknown column 'region' in 'where clause'. Why?

A Because 'region' column does not exist in orders table and is not prefixed with 'c.'.

B Because 'region' is ambiguous and needs table alias prefix.

C Because JOIN syntax is invalid in UPDATE.

D Because SET clause must come after WHERE clause.

💡 Hint

Check which table 'region' belongs to and how it is referenced.

The WHERE clause references 'region' without specifying the table alias. Since 'region' is in customers table, it must be referenced as 'c.region'. Without prefix, MySQL looks for 'region' in orders table and fails.

🧠 Conceptual

expert

What is the effect of this UPDATE with JOIN query?

Given tables students and scores, what does this query do?

UPDATE students s
JOIN scores sc ON s.id = sc.student_id
SET s.status = 'passed'
WHERE sc.score >= 60;

A Raises an error because UPDATE with JOIN cannot use WHERE on joined table.

B Marks all students as 'passed' regardless of scores.

C Marks students as 'passed' if they have at least one score 60 or above.

D Marks students as 'passed' only if all their scores are 60 or above.

💡 Hint

Think about how JOIN and WHERE filter rows for update.

The JOIN matches students with their scores. The WHERE filters scores >= 60. So students with at least one such score get updated. It does not check all scores.