UPDATE with subquery preview in SQL

Introduction

We use UPDATE with a subquery to change data in one table based on information from another table. It helps keep data accurate and connected.

When you want to update a customer's address based on their latest order details.

When you need to set employee salaries based on their department's average salary.

When you want to mark products as discontinued if they haven't sold in the last year.

When you want to update student grades based on scores stored in another table.

Syntax

SQL

UPDATE table_to_update
SET column_to_change = (SELECT value FROM another_table WHERE condition)
WHERE some_condition;

The subquery inside parentheses returns the new value for the column.

Make sure the subquery returns only one value for each row to update.

Examples

Update each employee's department name by looking it up from the departments table.

SQL

UPDATE employees
SET department = (SELECT dept_name FROM departments WHERE departments.id = employees.dept_id)
WHERE dept_id IS NOT NULL;

Set the price of all books to the average price of books.

SQL

UPDATE products
SET price = (SELECT AVG(price) FROM products WHERE category = 'Books')
WHERE category = 'Books';

Sample Program

We create two tables: employees and departments. Then we update the employees table to fill the department name by looking it up from departments using dept_id.

SQL

CREATE TABLE employees (id INT, name VARCHAR(20), dept_id INT, department VARCHAR(20));
CREATE TABLE departments (id INT, dept_name VARCHAR(20));

INSERT INTO employees VALUES (1, 'Alice', 10, NULL), (2, 'Bob', 20, NULL), (3, 'Carol', 10, NULL);
INSERT INTO departments VALUES (10, 'Sales'), (20, 'HR');

UPDATE employees
SET department = (SELECT dept_name FROM departments WHERE departments.id = employees.dept_id)
WHERE dept_id IS NOT NULL;

SELECT id, name, dept_id, department FROM employees ORDER BY id;

Important Notes

If the subquery returns more than one value, the UPDATE will fail with an error.

You can use JOINs instead of subqueries for better performance in some databases.

Always backup your data before running UPDATE queries to avoid accidental data loss.

Summary

UPDATE with subquery lets you change data based on other tables.

The subquery must return a single value for each row updated.

It helps keep related data consistent and up to date.

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