UPDATE with WHERE condition in SQL - Step-by-Step Execution

Concept Flow - UPDATE with WHERE condition

Start UPDATE statement

↓

Check WHERE condition for each row

↓

Update row

↓

End UPDATE

The UPDATE command checks each row against the WHERE condition. If true, it updates that row; if false, it skips it.

Execution Sample

SQL

UPDATE Employees
SET Salary = Salary + 500
WHERE Department = 'Sales';

This query increases the Salary by 500 for employees in the Sales department only.

Execution Table

Step	Row ID	Current Salary	Department	WHERE Condition (Department='Sales')	Action	New Salary
1	101	3000	Sales	True	Update	3500
2	102	3200	HR	False	Skip	3200
3	103	2800	Sales	True	Update	3300
4	104	4000	IT	False	Skip	4000
5	105	3100	Sales	True	Update	3600
6	-	-	-	-	End of rows	-

💡 All rows checked; only rows with Department='Sales' updated.

Variable Tracker

Row ID	Salary Before	Salary After
101	3000	3500
102	3200	3200
103	2800	3300
104	4000	4000
105	3100	3600

Key Moments - 2 Insights

Why are some rows not updated even though the UPDATE command runs?

Rows where the WHERE condition is False are skipped, as shown in execution_table rows 2 and 4.

What happens if the WHERE condition is missing?

Without WHERE, all rows would be updated. Here, only rows with Department='Sales' are updated.

Visual Quiz - 3 Questions

Look at the execution_table, what is the new salary of Row ID 103 after the update?

A 3500

B 2800

C 3300

D 3100

💡 Hint

Check the 'New Salary' column for Row ID 103 in the execution_table.

At which step does the WHERE condition evaluate to False?

A Step 2

B Step 3

C Step 1

D Step 5

💡 Hint

Look at the 'WHERE Condition' column in the execution_table for False values.

If the WHERE clause was removed, how would the 'Action' column change?

A All rows would be 'Skip'

B All rows would be 'Update'

C Only Sales rows would be 'Update'

D No rows would be updated

💡 Hint

Without WHERE, UPDATE affects every row as per SQL rules.

Concept Snapshot

UPDATE table_name
SET column = new_value
WHERE condition;

- Only rows matching WHERE condition are updated.
- Rows not matching are unchanged.
- Omitting WHERE updates all rows.

Full Transcript

The UPDATE statement changes data in a table. It checks each row against the WHERE condition. If the condition is true, it updates that row's data. If false, it leaves the row unchanged. For example, increasing salary only for employees in the Sales department updates only those rows. Rows in other departments remain the same. Without a WHERE clause, all rows would be updated. This visual shows step-by-step how each row is checked and updated or skipped.

Practice

1.

What does the WHERE clause do in an UPDATE statement?

easy

A. It adds new rows to the table.

B. It changes the table structure.

C. It limits which rows get updated.

D. It deletes rows from the table.

Solution

1. Step 1: Understand the purpose of UPDATE

   The UPDATE statement changes data in existing rows of a table.

2. Step 2: Role of WHERE clause in UPDATE

   The WHERE clause specifies which rows should be changed, so only those rows are updated.

3. Final Answer:

   It limits which rows get updated. -> Option C

4. Quick Check:

   WHERE clause = row filter [OK]

Hint: WHERE filters rows to update only those needed [OK]

Common Mistakes:

• Thinking WHERE adds or deletes rows
• Believing UPDATE changes table structure
• Ignoring WHERE updates all rows

2.

Which of the following is the correct syntax to update the salary to 5000 for employees with id 3?

UPDATE employees SET salary = 5000 WHERE id = 3;

easy

A. UPDATE employees SET salary 5000 WHERE id = 3;

B. UPDATE employees salary = 5000 WHERE id = 3;

C. UPDATE employees SET salary = 5000 id = 3;

D. UPDATE employees SET salary = 5000 WHERE id = 3;

Solution

1. Step 1: Check UPDATE syntax

   The correct syntax requires SET before column assignment and WHERE for condition.

2. Step 2: Validate each option

   UPDATE employees SET salary = 5000 WHERE id = 3; uses SET and WHERE correctly; others miss SET or WHERE or have wrong syntax.

3. Final Answer:

   UPDATE employees SET salary = 5000 WHERE id = 3; -> Option D

4. Quick Check:

   SET + WHERE = correct UPDATE syntax [OK]

Hint: Always use SET before column assignments in UPDATE [OK]

Common Mistakes:

• Omitting SET keyword
• Forgetting WHERE clause
• Missing equal sign (=) in assignment

3.

Given the table products:

id | name    | price
---+---------+-------
1  | Pen     | 10
2  | Pencil  | 5
3  | Eraser  | 3

What will be the result of this query?

UPDATE products SET price = price + 2 WHERE price < 10;

medium

A. All products will have their price increased by 2.

B. Only products with price less than 10 will have their price increased by 2.

C. No products will be updated because price is not changed.

D. Syntax error due to missing WHERE clause.

Solution

1. Step 1: Identify rows matching WHERE condition

   Prices less than 10 are 5 and 3 (Pencil and Eraser).

2. Step 2: Apply UPDATE only to matching rows

   Only Pencil and Eraser prices increase by 2, Pen remains 10.

3. Final Answer:

   Only products with price less than 10 will have their price increased by 2. -> Option B

4. Quick Check:

   WHERE filters rows updated [OK]

Hint: WHERE limits which rows get updated, others stay same [OK]

Common Mistakes:

• Assuming all rows update without WHERE
• Thinking price must be reset, not incremented
• Confusing syntax error with correct query

4.

Find the error in this query:

UPDATE employees SET salary = 6000 WHERE;

medium

A. Missing condition after WHERE clause.

B. Missing SET keyword.

C. Incorrect table name.

D. No error, query is correct.

Solution

1. Step 1: Analyze the WHERE clause

   The WHERE clause is incomplete; it must have a condition after it.

2. Step 2: Check other parts of query

   SET keyword and table name are correct; only WHERE is incomplete.

3. Final Answer:

   Missing condition after WHERE clause. -> Option A

4. Quick Check:

   WHERE needs a condition [OK]

Hint: WHERE must always have a condition after it [OK]

Common Mistakes:

• Leaving WHERE empty
• Confusing missing SET with WHERE error
• Assuming query runs without WHERE condition

5.

You have a table students with columns id, name, and grade. You want to increase the grade by 5 for all students who scored less than 50, but not exceed 50 after update. Which query correctly does this?

hard

A. UPDATE students SET grade = CASE WHEN grade + 5 > 50 THEN 50 ELSE grade + 5 END WHERE grade < 50;

B. UPDATE students SET grade = grade + 5 WHERE grade < 50 AND grade + 5 <= 50;

C. UPDATE students SET grade = grade + 5 WHERE grade < 50;

D. UPDATE students SET grade = 50 WHERE grade < 50;

Solution

1. Step 1: Understand the requirement

   Increase grade by 5 only for grades less than 50, but cap max grade at 50.

2. Step 2: Analyze each option

   UPDATE students SET grade = 50 WHERE grade < 50; sets all below 50 to 50 directly, no increase by 5. UPDATE students SET grade = grade + 5 WHERE grade < 50 AND grade + 5 <= 50; increases by 5 only if result ≤ 50, skipping some rows. UPDATE students SET grade = grade + 5 WHERE grade < 50; increases by 5 without capping at 50. UPDATE students SET grade = CASE WHEN grade + 5 > 50 THEN 50 ELSE grade + 5 END WHERE grade < 50; uses CASE to increase by 5 but caps at 50 correctly.

3. Final Answer:

   UPDATE students SET grade = CASE WHEN grade + 5 > 50 THEN 50 ELSE grade + 5 END WHERE grade < 50; -> Option A

4. Quick Check:

   Use CASE to cap values in UPDATE [OK]

Hint: Use CASE in UPDATE to handle conditional value changes [OK]

Common Mistakes:

• Not capping grade at 50
• Updating all rows without WHERE
• Skipping rows that would exceed 50