PostgreSQL Update

In this tutorial, we are going to learn the PostgreSQL UPDATE command for updating the current data in a table.

In PostgreSQL, the UPDATE command is used to change the present records in a table. To update the selected rows, we have to use the WHERE clause; otherwise, all rows would be updated.

Syntax of Update command

The syntax of update command is as follows:

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

We have the following parameters, which are used in the above syntax:

Examples of PostgreSQL update command

For our better understanding, we will see examples of PostgreSQL Update command.

We will take the department table, which we created in the Insert command section.

Firstly, we will see the structure of the department table with the help of below command:

SELECT * FROM department;

After executing the select command, we will get the below result:

Example of PostgreSQL UPDATE table

Here, we will modify the NULL values of the last_update column to the Current date with the below command's help:

UPDATE department
SET last_update = DEFAULT
WHERE
last_update IS NULL;

We will get the below outcome, after performing the above command:

In the above query, we apply the DEFAULT keyword to the last_update column that takes the current date as per the default value. And the WHERE clause updates only those rows where the last_update column value is NULL.

Example of upgrading all rows in a table using the Update command

To update the values in the location column as U.S.A for all rows in the department table, we use the following command:

Note: In the below update command, we ignore the WHERE clause:

UPDATE department
SET location = 'U.S.A';

After executing the above query, it will update the location column in the department table:

We will use the select command to verify the output of the above command in the department table:

select* from department;

And within the same table, we can also update data of a column from an additional column.

The below command is used to copies the dept_name column's values to the description column of the department table:

UPDATE department
SET description = dept_name;

Once we perform the above query, it will update the description column in the department table:

After that, we will use the select command to see the updated column(description) in the department table:

Example of PostgreSQL update joins command

Here we will take the department_tmp table, which has the same structure as the department table:

The following command is used to update values, which come from the department table for the columns in the department_tmp table:

UPDATE department_tmp
SET location = department.location,
description = department.description,
last_update = department.last_update
FROM
department
WHERE
department_tmp.Dept_id = department.Dept_id;

Once we perform the above query, it will update the department_tmp table:

To verify the department_tmp table, we will use the Select command:

SELECT * FROM department_tmp; 

And, we will get the below output:

Note: In the above update command, we used the FROM clause to describe the second table (department), which contains in the update.

Here we used the join condition in the WHERE clause. And sometimes this UPDATE command mentioned as UPDATE INNER JOIN or UPDATE JOIN as two or more tables are involved in the UPDATE command.

Update command through returning condition

By default, the update command can return the number of affected rows, and it also returns the efficient data with the help of the Returning section.

The below command is used to update the row with Dept_id 1 in the department table and return the updated data.

UPDATE department
SET description = 'Names of departments',
location = 'NewYork'
WHERE
dept_id = 1 
RETURNING dept_id,
description,
location;

After executing the above command, we will get the below table structure:

After that, we will use the select command to check the updated data in the department table:

SELECT
*
FROM
department
WHERE
dept_id = 1;

Once we run the above Select query, we get the below output: