UPDATE with expressions in SQL - Step-by-Step Execution

Concept Flow - UPDATE with expressions

Start UPDATE

↓

Identify rows to update

↓

Calculate new values using expressions

↓

Apply new values to rows

↓

Finish UPDATE

The UPDATE command finds rows to change, calculates new values using expressions, then applies these new values to the rows.

Execution Sample

SQL

UPDATE employees
SET salary = salary * 1.1
WHERE department = 'Sales';

This increases salary by 10% for employees in the Sales department.

Execution Table

Step	Row ID	Old salary	Condition (department='Sales')	Expression evaluation	New salary	Action
1	101	50000	True	50000 * 1.1 = 55000	55000	Update salary
2	102	60000	False	Skip	60000	No change
3	103	55000	True	55000 * 1.1 = 60500	60500	Update salary
4	104	70000	False	Skip	70000	No change
5	105	48000	True	48000 * 1.1 = 52800	52800	Update salary
6	-	-	-	-	-	End of rows

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

Variable Tracker

Row ID	salary (start)	salary (after update)
101	50000	55000
102	60000	60000
103	55000	60500
104	70000	70000
105	48000	52800

Key Moments - 2 Insights

Why are some salaries not changed even though the UPDATE runs on the whole table?

Only rows where the condition (department='Sales') is true get updated, as shown in execution_table rows 2 and 4 where condition is false and action is 'No change'.

How is the new salary calculated for each row?

The new salary is computed by multiplying the old salary by 1.1, as shown in the 'Expression evaluation' column in the execution_table.

Visual Quiz - 3 Questions

Look at the execution table, what is the new salary for Row ID 103 after the update?

A 55000

B 60500

C 60000

D 6050

💡 Hint

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

At which step does the condition department='Sales' evaluate to false?

A Step 2

B Step 1

C Step 3

D Step 5

💡 Hint

Look at the 'Condition' column in the execution_table for each step.

If the expression changed to salary = salary + 500, what would be the new salary for Row ID 105?

A 48500

B 53000

C 53300

D 52800

💡 Hint

Add 500 to the old salary 48000 from variable_tracker for Row ID 105.

Concept Snapshot

UPDATE table_name
SET column = expression
WHERE condition;

- Finds rows matching condition
- Calculates new values using expressions
- Updates rows with new values

Expressions can use old column values.

Full Transcript

The UPDATE statement changes data in a table. It first finds rows that match a condition. Then it calculates new values using expressions, often involving old column values. Finally, it updates those rows with the new values. For example, increasing salary by 10% for employees in Sales updates only those rows where department is 'Sales'. The execution table shows each row's old salary, whether it meets the condition, the calculation done, and the new salary applied. Rows not matching the condition remain unchanged. This step-by-step process helps understand how UPDATE with expressions works.

Practice

1. What does the SQL statement UPDATE employees SET salary = salary + 500 do?

easy

A. Sets all salaries to 500

B. Increases the salary of all employees by 500

C. Deletes employees with salary 500

D. Creates a new employee with salary 500

Solution

1. Step 1: Understand the UPDATE statement

   The statement updates the 'salary' column in the 'employees' table.

2. Step 2: Analyze the expression used

   It adds 500 to the current salary value for every row because no WHERE clause limits the rows.

3. Final Answer:

   Increases the salary of all employees by 500 -> Option B

4. Quick Check:

   salary = salary + 500 [OK]

Hint: No WHERE means update all rows with the expression [OK]

Common Mistakes:

• Thinking it sets salary to 500 instead of adding
• Confusing UPDATE with DELETE
• Assuming it adds 500 only to some rows without WHERE

2. Which of the following is the correct syntax to increase the price by 10% only for products in category 'Books'?

easy

A. UPDATE products SET price = price * 1.10 WHERE category = 'Books';

B. UPDATE products SET price = price + 10% WHERE category = 'Books';

C. UPDATE products SET price = price + 0.10 WHERE category = 'Books';

D. UPDATE products SET price = price * 10 WHERE category = 'Books';

Solution

1. Step 1: Identify correct expression for 10% increase

   Multiplying price by 1.10 increases it by 10%.

2. Step 2: Check WHERE clause for category filtering

   WHERE category = 'Books' limits update to only books.

3. Final Answer:

   UPDATE products SET price = price * 1.10 WHERE category = 'Books'; -> Option A

4. Quick Check:

   price * 1.10 with WHERE category = 'Books' [OK]

Hint: Use multiplication by 1.10 for 10% increase [OK]

Common Mistakes:

• Using + 10% which is invalid syntax
• Adding 0.10 instead of 10%
• Multiplying price by 10 instead of 1.10

3. Given the table accounts with columns id, balance, what will be the balances after running:
UPDATE accounts SET balance = balance - 100 WHERE balance >= 500;
Initial balances: 400, 500, 600

medium

A. [400, 600, 700]

B. [300, 400, 500]

C. [400, 500, 600]

D. [400, 400, 500]

Solution

1. Step 1: Identify which rows meet the WHERE condition

   Only balances 500 and 600 are >= 500, so only these rows update.

2. Step 2: Subtract 100 from qualifying balances

   500 - 100 = 400, 600 - 100 = 500; 400 remains unchanged.

3. Final Answer:

   [400, 400, 500] -> Option D

4. Quick Check:

   Only balances >= 500 reduced by 100 [OK]

Hint: Only rows matching WHERE get updated [OK]

Common Mistakes:

• Updating all rows ignoring WHERE
• Subtracting 100 from all balances
• Confusing >= with >

4. What is wrong with this SQL statement?
UPDATE orders SET total = total + discount WHERE discount;

medium

A. The SET expression cannot use other columns

B. The UPDATE statement is missing a table name

C. The WHERE clause is invalid because it lacks a comparison

D. The total column cannot be updated

Solution

1. Step 1: Analyze the WHERE clause

   WHERE discount is incomplete; it needs a condition like discount > 0.

2. Step 2: Check other parts of the statement

   Table name and SET expression are correct; updating with another column is valid.

3. Final Answer:

   The WHERE clause is invalid because it lacks a comparison -> Option C

4. Quick Check:

   WHERE must have a condition, not just a column name [OK]

Hint: WHERE needs a condition, not just a column name [OK]

Common Mistakes:

• Assuming column names alone are valid WHERE conditions
• Thinking SET cannot use other columns
• Ignoring missing comparison in WHERE

5. You want to increase the salary by 5% for employees whose current salary is below the average salary. Which SQL statement correctly does this?

hard

A. UPDATE employees SET salary = salary * 1.05 WHERE salary < (SELECT AVG(salary) FROM employees);

B. UPDATE employees SET salary = salary + 5 WHERE salary < AVG(salary);

C. UPDATE employees SET salary = salary * 1.05 WHERE salary > (SELECT AVG(salary) FROM employees);

D. UPDATE employees SET salary = salary * 0.95 WHERE salary < (SELECT AVG(salary) FROM employees);

Solution

1. Step 1: Use a subquery to get average salary

   The subquery (SELECT AVG(salary) FROM employees) returns the average salary.

2. Step 2: Apply 5% increase only to salaries below average

   WHERE salary < average filters employees below average; salary * 1.05 increases salary by 5%.

3. Final Answer:

   UPDATE employees SET salary = salary * 1.05 WHERE salary < (SELECT AVG(salary) FROM employees); -> Option A

4. Quick Check:

   Use subquery in WHERE and multiply salary by 1.05 [OK]

Hint: Use subquery in WHERE and multiply salary by 1.05 [OK]

Common Mistakes:

• Using AVG(salary) without subquery
• Adding 5 instead of 5%
• Updating salaries above average instead of below
• Multiplying by 0.95 which decreases salary