UPDATE with expressions in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SQL UPDATE statement do?

The UPDATE statement changes existing data in a table by modifying one or more columns for rows that match a condition.

beginner

How can you use expressions in an UPDATE statement?

You can use expressions to calculate new values based on existing column values, like adding 10 to a salary: SET salary = salary + 10.

beginner

Example: What does this SQL do?
UPDATE employees SET salary = salary * 1.05 WHERE department = 'Sales';

It increases the salary by 5% for all employees in the Sales department.

intermediate

Can you use functions in UPDATE expressions? Give an example.

Yes, you can use functions. For example, to set a date column to the current date: UPDATE orders SET shipped_date = CURRENT_DATE WHERE shipped_date IS NULL;

beginner

Why is it important to use a WHERE clause in an UPDATE statement with expressions?

Without a WHERE clause, the UPDATE will change all rows in the table, which might cause unintended data changes.

What happens if you run an UPDATE statement without a WHERE clause?

A Only the first row is updated

B No rows are updated

C All rows in the table are updated

D The statement will cause an error

Without a WHERE clause, the UPDATE affects every row in the table.

Which of the following is a valid expression in an UPDATE statement?

A SET price = price - 'ten'

B SET price = price + 10

C SET price = price & 10

D SET price = 'price + 10'

You can add numbers directly in expressions like price = price + 10. The others are invalid or use wrong syntax.

How do you increase a numeric column 'score' by 20% using UPDATE?

A SET score = score * 1.2

B SET score = score + 20

C SET score = score / 1.2

D SET score = score - 20

Multiplying by 1.2 increases the value by 20%.

Which SQL function can you use to set a date column to today’s date in an UPDATE?

A CURRENT_DATE

B NOW()

C GETDATE()

D TODAY()

CURRENT_DATE is standard SQL for today's date. NOW() and GETDATE() are specific to some databases; TODAY() is not standard.

What does this statement do?
UPDATE products SET stock = stock - 1 WHERE product_id = 100;

A Deletes product 100

B Sets stock to 1 for product 100

C Increases stock by 1 for product 100

D Decreases stock by 1 for product 100

It subtracts 1 from the stock column for the product with ID 100.

Explain how to use expressions in an UPDATE statement to modify data.

Think about changing values based on current data.

Describe why the WHERE clause is important in UPDATE statements with expressions.

Consider what happens if you forget WHERE.

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