Why UPDATE with expressions in SQL? - Purpose & Use Cases

The Big Idea

What if you could change hundreds of prices with just one simple command?

The Scenario

Imagine you have a big list of products with prices in a spreadsheet. You want to increase all prices by 10%. Doing this by hand means opening each row and typing the new price one by one.

The Problem

Manually changing each price is slow and boring. It's easy to make mistakes, like typing the wrong number or missing some rows. If you have hundreds or thousands of products, this becomes a huge headache.

The Solution

Using UPDATE with expressions in SQL lets you change many rows at once by calculating new values automatically. You can tell the database to increase prices by 10% in one simple command, saving time and avoiding errors.

Before vs After

✗ Before

Update each price cell manually in the spreadsheet.

✓ After

UPDATE products SET price = price * 1.10;

What It Enables

This lets you quickly and safely adjust data in bulk, making your work faster and more reliable.

Real Life Example

A store owner wants to raise all product prices by 10% because of increased costs. Instead of editing each price, they run one SQL command to update all prices instantly.

Key Takeaways

Manual updates are slow and error-prone.

UPDATE with expressions automates bulk changes.

It saves time and reduces mistakes.

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