UPDATE with subquery preview in SQL - Time & Space Complexity

Time Complexity: UPDATE with subquery preview

O(n)

Understanding Time Complexity

When we update data using a subquery, the database must find matching rows before changing values.

We want to know how the work grows as the data gets bigger.

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

UPDATE employees
SET salary = salary * 1.1
WHERE department_id IN (
  SELECT department_id
  FROM departments
  WHERE location = 'New York'
);
    

This code increases salaries by 10% for employees in departments located in New York.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Checking each employee to see if their department matches one from the subquery.
• How many times: Once for each employee row in the table.

How Execution Grows With Input

As the number of employees grows, the database must check more rows against the subquery results.

Input Size (n employees)	Approx. Operations
10	About 10 checks
100	About 100 checks
1000	About 1000 checks

Pattern observation: The work grows roughly in direct proportion to the number of employees.

Final Time Complexity

Time Complexity: O(n)

This means the time to update grows linearly with the number of employees.

Common Mistake

[X] Wrong: "The subquery runs once and does not affect the update time much."

[OK] Correct: The subquery results must be matched against each employee, so its size and complexity impact the total work.

Interview Connect

Understanding how updates with subqueries scale helps you write efficient queries and explain your reasoning clearly.

Self-Check

"What if the subquery returned a very large list of department IDs? How would that affect the time complexity?"

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