UPDATE with subquery preview in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of UPDATE with correlated subquery

Consider the tables employees and departments. The employees table has columns id, name, salary, and department_id. The departments table has columns id and budget.

What will be the salary of employee with id = 3 after running this query?

SQL

UPDATE employees e
SET salary = (
  SELECT budget / 10
  FROM departments d
  WHERE d.id = e.department_id
)
WHERE e.id = 3;

A 5000

B 10000

C NULL

D No change (salary remains the same)

💡 Hint

Think about how the subquery uses the department_id of the employee to find the department's budget.

The subquery selects the budget from the departments table where the id matches the employee's department_id. Then it divides that budget by 10 and sets it as the new salary for the employee with id = 3.

🧠 Conceptual

intermediate

Understanding UPDATE with subquery behavior

Which statement best describes what happens when you run an UPDATE statement with a subquery in the SET clause?

A The subquery is ignored if it returns more than one row.

B The subquery is executed only once before the update starts and its result is used for all rows.

C The subquery is executed once for each row being updated, using values from that row.

D The subquery must not reference the table being updated.

💡 Hint

Think about how the subquery can use values from the row being updated.

In an UPDATE with a correlated subquery, the subquery runs once per row, using that row's values to compute the new value.

📝 Syntax

advanced

Identify the syntax error in UPDATE with subquery

Which option contains a syntax error in the UPDATE statement using a subquery?

SQL

UPDATE products p
SET price = (SELECT AVG(price) FROM products WHERE category = p.category)
WHERE category = 'Electronics';

A UPDATE products SET price = (SELECT AVG(price) FROM products WHERE category = products.category) WHERE category = 'Electronics';

B UPDATE products p SET price = SELECT AVG(price) FROM products WHERE category = p.category WHERE category = 'Electronics';

C UPDATE products p SET price = (SELECT AVG(price) FROM products WHERE category = p.category);

D UPDATE products p SET price = (SELECT AVG(price) FROM products WHERE category = p.category) WHERE category = 'Electronics';

💡 Hint

Look carefully at the placement of parentheses and keywords.

Option B is missing parentheses around the subquery and has an extra WHERE clause outside the subquery, causing syntax error.

❓ optimization

advanced

Optimizing UPDATE with subquery for performance

You want to update the salary of all employees to the average salary of their department. Which query is more efficient?

A UPDATE employees e SET salary = (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

B UPDATE employees SET salary = (SELECT AVG(salary) FROM employees);

C UPDATE employees SET salary = (SELECT salary FROM employees WHERE id = employees.id);

D UPDATE employees SET salary = avg_salary FROM (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) d WHERE employees.department_id = d.department_id;

💡 Hint

Consider how many times the subquery runs in each option.

Option D calculates average salaries per department once in a derived table, then joins to update, reducing repeated subquery executions.

🔧 Debug

expert

Why does this UPDATE with subquery cause an error?

Given the query below, why does it cause an error?

SQL

UPDATE orders o
SET total = (SELECT SUM(amount) FROM order_items WHERE order_id = o.id)
WHERE o.id IN (SELECT id FROM orders WHERE status = 'pending');

A The subquery is valid and causes no error.

B The subquery returns multiple rows causing a runtime error.

C The subquery returns NULL for some rows causing the update to fail.

D The subquery references the same table being updated causing a conflict.

💡 Hint

Think about whether the subquery returns a single value per row.

The subquery returns a single sum value per order, so it is valid. The WHERE clause limits updates to pending orders only.

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