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.