PostgreSQL UPDATE Statement

The PostgreSQL UPDATE statement is an important SQL command used to modify existing data in one or more rows of a table. It allows users to update specific columns or multiple columns at once, using conditions defined in the WHERE clause. This command is highly flexible, enabling dynamic data management and targeted updates.

In this article, we will explain how to efficiently use the PostgreSQL UPDATE statement, covering syntax, practical examples, and advanced techniques such as updating multiple rows, handling updates without a WHERE clause, and using the RETURNING clause..

PostgreSQL UPDATE Statement

• In PostgreSQL, the UPDATE statement plays an important role in modifying the data that already exists in a table.
  
• This command allows users to update one or more columns based on specific conditions, making it essential for maintaining and managing dynamic data in a database.
  
• We can apply changes to specific rows based on conditions provided in the WHERE clause, making it a flexible and powerful tool for managing dynamic data.

Syntax:

UPDATE table_name
SET column1 = value1,
    column2 = value2, ...
WHERE condition;

Key terms:

• Table Specification: Identify the table where the changes are intended.
  
• SET Clause: List the columns whose values need to be updated.
  
• WHERE Clause: Define the condition to specify which rows should be updated.

Setting Up a Sample Database and Table

Before we proceed with examples, let’s create a sample database and table to demonstrate the UPDATE statement. Create a database named "company" to demonstrate various UPDATE commands

Query:

CREATE DATABASE company;
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
);

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);

SELECT * FROM employee;

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:

Output

Examples of PostgreSQL UPDATE Statement

Here are a few examples to help us understand the PostgreSQL UPDATE statement. These examples demonstrate how to modify data in a table, update single or multiple rows, and use conditions to make targeted changes.

Example 1: Updating a Single Row

In this example, we will update the last name of an employee whose first name is "Raju." Let's change Raju Kumar to Raju Singh. This ensures that only the row with the matching first name "Raju" is updated, demonstrating the use of the WHERE clause to target specific rows in the PostgreSQL UPDATE statement.

Query:

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

Output

Explanation:

This query updates a single row in the employee table, modifying the value of the last_name column where the first name is 'Raju'. The WHERE clause ensures only the correct row is modified. The last name of Raju Kumar is updated to Raju Singh

Example 2: Updating Multiple Rows

In the above we made an update to a single row but here we update the last name of multiple employees. Let's change the last name of all employees who currently have the last name 'Aggarwal' to 'Gupta'.

Query:

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

Output

Explanation:

This query applies changes to multiple rows in the table, ensuring that all rows meeting the condition in the WHERE clause are updated. All employees with the last name 'Aggarwal' will have their last name changed to 'Gupta'.

Example 3: Updating Multiple Columns

In this example, we update multiple columns for a specific employee by setting both first_name and last_name values. The WHERE clause ensures that only the row where employee_id is 8 is modified, updating the employee's name to MS Dhoni. This demonstrates how to modify multiple columns in a single update statement.

Query:

UPDATE employee
SET first_name = 'MS',
    last_name = 'Dhoni'
WHERE employee_id = 8;

Output

UPDATE 1

Explanation:

This query updates multiple columns in a single row, demonstrating the flexibility of the SET clause.

Conclusion

Updating data efficiently is an important aspect of database management. The PostgreSQL UPDATE statement is a flexible tool for modifying existing data in a database. With its ability to update single or multiple columns and rows, it plays a crucial role in maintaining data accuracy. Features like the RETURNING clause make it even more powerful by allowing real-time validation of updates.