UPDATE: Change Existing Data

The UPDATE statement changes existing data in a table.
You can change one column or many columns, in one row or in many rows.


Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Important: Always use a WHERE clause, unless you really want to change every row.


Example 1: Update One Column

UPDATE employees
SET age = 29
WHERE id = 1;

Output:

UPDATE 1

This changes the age of the employee with id = 1 to 29. psql answers UPDATE 1, because one row was changed.


Example 2: Update Several Columns

UPDATE employees
SET department = 'Sales', age = 34
WHERE name = 'Thomas';

SELECT * FROM employees WHERE name = 'Thomas';

Output:

UPDATE 1
 id |  name  | age | department | gender 
----+--------+-----+------------+--------
 20 | Thomas |  34 | Sales      | male
(1 row)

This changes both the department and the age for Thomas.


Example 3: Use the Old Value

The new value can use the old one. This adds 1 to the age of every HR employee:

UPDATE employees
SET age = age + 1
WHERE department = 'HR';

Output:

UPDATE 2

Common Mistake: Forgetting the WHERE Clause

UPDATE employees
SET age = 30;

Mistake: This sets the age to 30 for every employee. psql would answer UPDATE 20.

Tip: Always add a WHERE condition to choose the rows you want to change.


PostgreSQL Tip: Row Order After an Update

After an update, a changed row can come back in a different place when you read the table without ORDER BY:

SELECT id, name FROM employees WHERE id <= 3;

Output:

 id | name  
----+-------
  2 | Bob
  3 | John
  1 | Alice
(3 rows)

Alice (id 1) was changed in Example 1, and now she comes after Bob and John. Add ORDER BY id whenever you need the rows in a fixed order.

In the exercise, move Emma to another department.