PostgreSQL - UPDATE Table

In this tutorial we will be learning to UPDATE rows in PostgreSQL.

Feel free to check out SELECT query tutorial PostgreSQL - SELECT FROM Table.

To update rows of a table in Postgres we use UPDATE table_name statement.

Update syntax

UPDATE table_name SET column_name = value WHERE condition;

Update single column of a single row using primary key

In the following example we are going to update the score of an employee having id e05.

Before UPDATE:

SELECT * FROM employee WHERE "employeeId" = 'e05';


 employeeId | firstName | lastName | score |   lastModifiedAt    |      createdAt      |  birthday  |        email
------------+-----------+----------+-------+---------------------+---------------------+------------+----------------------
 e05        | Bob       | Coder    |     5 | 2018-01-01 01:02:10 | 2018-01-01 01:02:10 | 1900-08-20 | bobcoder@example.com
(1 row)

UPDATE Query:

UPDATE employee SET score = 8 WHERE "employeeId" = 'e05';

Output

UPDATE 1

Employee data after UPDATE:

SELECT * FROM employee WHERE "employeeId" = 'e05';
 employeeId | firstName | lastName | score |   lastModifiedAt    |      createdAt      |  birthday  |        email
------------+-----------+----------+-------+---------------------+---------------------+------------+----------------------
 e05        | Bob       | Coder    |     8 | 2018-01-01 01:02:10 | 2018-01-01 01:02:10 | 1900-08-20 | bobcoder@example.com
(1 row)

Update multiple columns of a single row using primary key

In order to update multiple columns we separate them using comma.

In the following example we are going to update the score and lastModifiedAt timestamp of employee having id e05.

UPDATE employee
SET score = 7, "lastModifiedAt" = '2018-01-02 03:04:05'
WHERE "employeeId" = 'e05';

After update we get the following result.

SELECT * FROM employee WHERE "employeeId" = 'e05';


 employeeId | firstName | lastName | score |   lastModifiedAt    |      createdAt      |  birthday  |        email
------------+-----------+----------+-------+---------------------+---------------------+------------+----------------------
 e05        | Bob       | Coder    |     7 | 2018-01-02 03:04:05 | 2018-01-01 01:02:10 | 1900-08-20 | bobcoder@example.com
(1 row)

Update columns of multiple rows

In the following example we are updating the score and the lastModifiedAt value for all the employees having lastName as Doe.

Before update

SELECT * FROM employee WHERE "lastName" = 'Doe';

 employeeId | firstName | lastName | score |   lastModifiedAt    |      createdAt      |  birthday  |        email
------------+-----------+----------+-------+---------------------+---------------------+------------+---------------------
 e02        | John      | Doe      |     8 | 2018-01-01 01:01:04 | 2018-01-01 01:01:04 | 1900-02-03 | johndoe@example.com
 e03        | Jane      | Doe      |     6 | 2018-01-01 01:01:04 | 2018-01-01 01:01:04 | 1900-05-20 | janedoe@example.com
(2 rows)

UPDATE employee
SET score = 9, "lastModifiedAt" = '2018-01-02 03:04:05'
WHERE "lastName" = 'Doe';

We get the following output:

UPDATE 2

The updated rows:

SELECT * FROM employee WHERE "lastName" = 'Doe';


 employeeId | firstName | lastName | score |   lastModifiedAt    |      createdAt      |  birthday  |        email
------------+-----------+----------+-------+---------------------+---------------------+------------+---------------------
 e02        | John      | Doe      |     9 | 2018-01-02 03:04:05 | 2018-01-01 01:01:04 | 1900-02-03 | johndoe@example.com
 e03        | Jane      | Doe      |     9 | 2018-01-02 03:04:05 | 2018-01-01 01:01:04 | 1900-05-20 | janedoe@example.com
(2 rows)

Update without WHERE clause

When we don't set the WHERE clause in our UPDATE statement then all the rows in the table gets updated.

Updating a table without the WHERE clause is dangerous. You may update rows that you never wanted to update.

Before UPDATE:

SELECT * FROM employee;


 employeeId | firstName | lastName | score |   lastModifiedAt    |      createdAt      |  birthday  |        email
------------+-----------+----------+-------+---------------------+---------------------+------------+----------------------
 e01        | Yusuf     | Shakeel  |     0 | 2021-01-01 01:01:01 | 2021-01-01 01:01:01 | 1900-01-01 | yusuf@example.com
 e04        | Tin       | Tin      |     7 | 2018-01-01 01:02:03 | 2018-01-01 01:02:03 | 1900-10-20 | tintin@example.com
 e05        | Bob       | Coder    |     7 | 2018-01-02 03:04:05 | 2018-01-01 01:02:10 | 1900-08-20 | bobcoder@example.com
 e02        | John      | Doe      |     9 | 2018-01-02 03:04:05 | 2018-01-01 01:01:04 | 1900-02-03 | johndoe@example.com
 e03        | Jane      | Doe      |     9 | 2018-01-02 03:04:05 | 2018-01-01 01:01:04 | 1900-05-20 | janedoe@example.com
(5 rows)

UPDATE Query:

Note in the following UPDATE statement we are not setting the WHERE clause. Hence all the rows will get updated.

UPDATE employee SET "lastModifiedAt" = '2018-02-04 06:08:10';

Output:

UPDATE 5

After UPDATE:

SELECT * FROM employee;


 employeeId | firstName | lastName | score |   lastModifiedAt    |      createdAt      |  birthday  |        email
------------+-----------+----------+-------+---------------------+---------------------+------------+----------------------
 e01        | Yusuf     | Shakeel  |     0 | 2018-02-04 06:08:10 | 2021-01-01 01:01:01 | 1900-01-01 | yusuf@example.com
 e04        | Tin       | Tin      |     7 | 2018-02-04 06:08:10 | 2018-01-01 01:02:03 | 1900-10-20 | tintin@example.com
 e05        | Bob       | Coder    |     7 | 2018-02-04 06:08:10 | 2018-01-01 01:02:10 | 1900-08-20 | bobcoder@example.com
 e02        | John      | Doe      |     9 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 | 1900-02-03 | johndoe@example.com
 e03        | Jane      | Doe      |     9 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 | 1900-05-20 | janedoe@example.com
(5 rows)

Don't forget to practice. Have fun learning.