UPDATE with WHERE clause in MySQL - Step-by-Step Execution

Concept Flow - UPDATE with WHERE clause

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 leaves it unchanged.

Execution Sample

MySQL

UPDATE employees
SET salary = salary + 500
WHERE department = 'Sales';

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

Execution Table

Step	Row ID	Current salary	Department	WHERE condition (department='Sales')	Action	New salary
1	1	3000	Sales	True	Update salary	3500
2	2	3200	HR	False	Skip row	3200
3	3	2800	Sales	True	Update salary	3300
4	4	4000	IT	False	Skip row	4000
5	5	3100	Sales	True	Update salary	3600
6	-	-	-	-	End of table	-

💡 All rows checked; only rows with department 'Sales' updated.

Variable Tracker

Variable	Start	After Row 1	After Row 2	After Row 3	After Row 4	After Row 5	Final
salary_row_1	3000	3500	3500	3500	3500	3500	3500
salary_row_2	3200	3200	3200	3200	3200	3200	3200
salary_row_3	2800	2800	2800	3300	3300	3300	3300
salary_row_4	4000	4000	4000	4000	4000	4000	4000
salary_row_5	3100	3100	3100	3100	3100	3600	3600

Key Moments - 3 Insights

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

Rows not matching the WHERE condition are skipped, as shown in execution_table rows 2 and 4 where the department is not 'Sales'.

Does the UPDATE affect all rows if WHERE is omitted?

No WHERE means all rows match condition by default, so all rows would be updated. Here, only rows with 'Sales' are updated.

What happens if the WHERE condition is always false?

No rows get updated. The execution_table would show all rows skipped, similar to rows 2 and 4 here.

Visual Quiz - 3 Questions

Look at the execution_table, what is the new salary of row 3 after the update?

A 3300

B 2800

C 3500

D 3100

💡 Hint

Check the 'New salary' column for Row ID 3 in the execution_table.

At which step does the WHERE condition evaluate to false for the first time?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Look at the 'WHERE condition' column in execution_table rows.

If the WHERE clause was removed, how would the action column change in the execution_table?

A All rows would show 'Skip row'.

B All rows would show 'Update salary'.

C Only rows with salary > 3000 would update.

D No rows would be updated.

💡 Hint

Without WHERE, all rows match condition by default, so all get updated.

Concept Snapshot

UPDATE table_name
SET column = value
WHERE condition;

- Only rows matching WHERE condition are updated.
- Rows not matching remain 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 columns as specified. If false, it leaves the row unchanged. For example, increasing salary by 500 only for employees in the Sales department updates only those rows. Rows in other departments are skipped. This selective update prevents unwanted changes. If WHERE is omitted, all rows get updated. This trace 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 in MySQL?

easy

A. It specifies which rows should be updated.

B. It creates a new table.

C. It deletes rows from the table.

D. It selects rows to display.

Solution

1. Step 1: Understand the purpose of UPDATE

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

2. Step 2: Role of WHERE clause

   The WHERE clause limits which rows get changed. Without it, all rows update.

3. Final Answer:

   It specifies which rows should be updated. -> Option A

4. Quick Check:

   WHERE limits rows to update [OK]

Hint: WHERE filters rows to update; no WHERE updates all [OK]

Common Mistakes:

• Thinking WHERE creates or deletes rows
• Confusing UPDATE with SELECT
• Ignoring WHERE causes all rows to update

2. Which of the following is the correct syntax to update the salary to 5000 for employees with id 10 in MySQL?

easy

A. UPDATE employees SET salary = 5000 AND id = 10;

B. UPDATE employees SET salary = 5000 WHERE id = 10;

C. UPDATE employees SET salary WHERE id = 10 = 5000;

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

Solution

1. Step 1: Identify correct UPDATE syntax

   The correct order is: UPDATE table SET column = value WHERE condition.

2. Step 2: Check each option

   UPDATE employees SET salary = 5000 WHERE id = 10; follows correct syntax. Others have misplaced clauses or wrong operators.

3. Final Answer:

   UPDATE employees SET salary = 5000 WHERE id = 10; -> Option B

4. Quick Check:

   UPDATE SET column=value WHERE condition [OK]

Hint: SET comes before WHERE in UPDATE statements [OK]

Common Mistakes:

• Placing WHERE before SET
• Using AND instead of WHERE
• Incorrect assignment syntax

3. Given the table products with columns id, name, and price, what will be the price of product with id = 3 after running this query?

UPDATE products SET price = price + 10 WHERE id = 3;

Assume the original price for id = 3 was 50.

medium

A. 10

B. 50

C. 60

D. NULL

Solution

1. Step 1: Understand the UPDATE operation

   The query adds 10 to the current price for the product where id = 3.

2. Step 2: Calculate new price

   Original price is 50, so 50 + 10 = 60.

3. Final Answer:

   60 -> Option C

4. Quick Check:

   50 + 10 = 60 [OK]

Hint: Add to existing value only for rows matching WHERE [OK]

Common Mistakes:

• Ignoring WHERE and updating all rows
• Setting price to 10 instead of adding
• Confusing original and updated values

4. Identify the error in this MySQL query:

UPDATE users SET age = 30 WHERE;

medium

A. SET keyword is misplaced.

B. Table name is missing.

C. UPDATE keyword is misspelled.

D. Missing condition after WHERE clause.

Solution

1. Step 1: Analyze the query structure

   The query has UPDATE, SET, and WHERE keywords but WHERE has no condition.

2. Step 2: Identify the error

   WHERE must be followed by a condition to specify which rows to update.

3. Final Answer:

   Missing condition after WHERE clause. -> Option D

4. Quick Check:

   WHERE requires condition [OK]

Hint: WHERE must always have a condition [OK]

Common Mistakes:

• Leaving WHERE empty
• Misplacing SET and WHERE
• Forgetting semicolon

5. You have a table orders with columns order_id, status, and amount. You want to increase the amount by 20% only for orders with status 'pending' and amount less than 100. Which query correctly does this?

hard

A. UPDATE orders SET amount = amount * 1.2 WHERE status = 'pending' AND amount < 100;

B. UPDATE orders SET amount = amount + 20 WHERE status = 'pending' OR amount < 100;

C. UPDATE orders SET amount = amount * 1.2 WHERE status = 'pending' OR amount < 100;

D. UPDATE orders SET amount = amount * 0.2 WHERE status = 'pending' AND amount < 100;

Solution

1. Step 1: Understand the condition

   We want to update only rows where status is 'pending' AND amount is less than 100.

2. Step 2: Calculate new amount

   Increase by 20% means multiply amount by 1.2.

3. Step 3: Check options

   UPDATE orders SET amount = amount * 1.2 WHERE status = 'pending' AND amount < 100; correctly uses AND and multiplies amount by 1.2. Others use OR or wrong calculations.

4. Final Answer:

   UPDATE orders SET amount = amount * 1.2 WHERE status = 'pending' AND amount < 100; -> Option A

5. Quick Check:

   AND condition + *1.2 [OK]

Hint: Use AND for multiple conditions in WHERE [OK]

Common Mistakes:

• Using OR instead of AND
• Adding fixed 20 instead of 20%
• Multiplying by 0.2 instead of 1.2