PostgreSQL - UPDATE Table
PostgreSQL
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.
ADVERTISEMENT