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 table_name
UPDATE table_name SET column_name = value WHERE condition;
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)
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)
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)
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.
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)
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.