PostgreSQL has an UPDATE statement that is used to update or modify an existing data in the table.
SET column1 = value1,
column2 = value2, ...
Let’s analyze the above syntax:
- First step is to specify the table where the changes are supposed to be made.
- Then we list the columns whose value is to be updated using the SET clause.
- The final step is to determine which rows you want to update exactly using the WHERE clause.
Let’s set up a sample database and table for the demonstration of UPDATE statement.
- Create a database named “company” with the below command:
CREATE DATABASE company;
- Add a table of “employee” to show the company hierarchy into the database using the below command:
CREATE TABLE employee (
employee_id INT PRIMARY KEY,
first_name VARCHAR (255) NOT NULL,
last_name VARCHAR (255) NOT NULL,
FOREIGN KEY (manager_id)
REFERENCES employee (employee_id)
ON DELETE CASCADE
- Now add some employee data to the table using the below command:
INSERT INTO employee (
(1, 'Sandeep', 'Jain', NULL),
(2, 'Abhishek ', 'Kelenia', 1),
(3, 'Harsh', 'Aggarwal', 1),
(4, 'Raju', 'Kumar', 2),
(5, 'Nikhil', 'Aggarwal', 2),
(6, 'Anshul', 'Aggarwal', 2),
(7, 'Virat', 'Kohli', 3),
(8, 'Rohit', 'Sharma', 3);
The value in the manager_id column represents the senior manager who the employee reports to. If it’s Null, he/she doesn’t report to anyone.
The overall hierarchy looks like the below image:
Our current tables looks like below:
Now let’s update data in the above mentioned table.
Here we will update the employee “Raju Kumar” name to “Raju Singh” using the UPDATE statement.
SET last_name = 'Singh'
WHERE first_name = 'Raju';
In the above we made an update to a single row, but here we will make changes to multiple rows. Here we will change the last name of everyone to ‘Gupta’ whose last name is ‘Aggarwal’.
SET last_name = 'Gupta'
WHERE last_name = 'Aggarwal';