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.