PostgreSQL – UPDATE

PostgreSQL has an UPDATE statement that is used to update or modify an existing data in the table.

Syntax:
UPDATE table
SET column1 = value1,
    column2 = value2, ...
WHERE
    condition;

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,
      manager_id INT,
      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 (
      employee_id,
      first_name,
      last_name,
      manager_id
  )
  VALUES
      (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.

  Example 1:
  Here we will update the employee “Raju Kumar” name to “Raju Singh” using the UPDATE statement.

  UPDATE employee 
  SET last_name = 'Singh'
  WHERE first_name = 'Raju';

  Output:
  

  Example 2:
  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’.

  UPDATE employee
  SET last_name = 'Gupta'
  WHERE last_name = 'Aggarwal';

  Output: