UPDATE with expressions in SQL

Introduction

We use UPDATE with expressions to change data in a table based on calculations or other column values. This helps keep data accurate and up-to-date automatically.

You want to increase all employees' salaries by 10%.

You need to set a discount price by subtracting 5 from the original price.

You want to mark orders as 'shipped' when the shipping date is today.

You want to update a score by adding points earned in the last game.

Syntax

SQL

UPDATE table_name
SET column_name = expression
WHERE condition;

The expression can use numbers, other columns, or functions.

The WHERE clause limits which rows get updated. Without it, all rows change.

Examples

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

SQL

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

This lowers the price by 5 for all books.

SQL

UPDATE products
SET price = price - 5
WHERE category = 'Books';

This marks orders as shipped if their ship date is today.

SQL

UPDATE orders
SET status = 'shipped'
WHERE ship_date = CURRENT_DATE;

Sample Program

This example creates an employees table, adds three employees, then increases salary by 10% for those in Sales. Finally, it shows all employees with updated salaries.

SQL

CREATE TABLE employees (
  id INT,
  name VARCHAR(50),
  salary DECIMAL(10,2),
  department VARCHAR(50)
);

INSERT INTO employees VALUES
(1, 'Alice', 50000, 'Sales'),
(2, 'Bob', 60000, 'HR'),
(3, 'Charlie', 55000, 'Sales');

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

SELECT id, name, salary, department FROM employees ORDER BY id;

Important Notes

Always back up your data before running UPDATE queries.

Test your UPDATE with a SELECT first to see which rows will change.

Use expressions to automate updates without manual calculations.

Summary

UPDATE with expressions changes data using calculations or other columns.

Use WHERE to update only certain rows.

This helps keep data accurate and saves time.

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