UPDATE multiple columns in SQL - Time & Space Complexity

Time Complexity: UPDATE multiple columns

O(n)

Understanding Time Complexity

When we update multiple columns in a database table, it is important to understand how the time it takes grows as the table gets bigger.

We want to know how the work changes when there are more rows to update.

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

UPDATE employees
SET salary = salary * 1.05,
    bonus = bonus + 1000
WHERE department = 'Sales';
    

This code updates the salary and bonus columns for all employees in the Sales department.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: The database scans each row to check if it belongs to the Sales department and then updates the columns.
• How many times: This happens once for each row in the table, but only rows matching the condition are updated.

How Execution Grows With Input

As the number of rows grows, the database must check more rows to find those in the Sales department.

Input Size (n)	Approx. Operations
10	About 10 checks and some updates
100	About 100 checks and more updates
1000	About 1000 checks and many updates

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

Final Time Complexity

Time Complexity: O(n)

This means the time to update grows linearly with the number of rows in the table.

Common Mistake

[X] Wrong: "Updating multiple columns takes more time than updating one column for each row."

[OK] Correct: The main cost is finding the rows to update. Changing more columns in those rows adds little extra time compared to scanning the rows.

Interview Connect

Understanding how updates scale helps you explain database performance clearly and shows you know what affects query speed.

Self-Check

"What if we removed the WHERE clause and updated all rows? How would the time complexity change?"