PostgreSQL: Update Data in a Table

Here you will learn how to update data in the table in PostgreSQL database.

In PostgreSQL, use the UPDATE statement to modify existing data in the table. The UPDATE statement only updates data in the table and does not modify the structure of a table.

Syntax: Update Table

UPDATE <table_name>
SET <column1> = <value1>,
    <column2> = <value2>,
    ...
WHERE <condition>
RETURNING * | <output_expression> AS <output_name>;

        

In above syntax:

Specify the name of the table you want to update data after the UPDATE keyword.
After the SET keyword, specify one or more combinations of column and their new values. The columns which are not specified in the SET clause will have their original values.
The WHERE clause is optional which limits the update operation specific to the specified condition. If you do not specify the WHERE clause, Postgres will update all the rows of a table.
The RETURNING clause is optional which will return a list of all updated rows or values of the specified column.

Let's update data in the following employee table.

Updating a Single Row

Use the WHERE clause with the UPDATE statement and specify a primary key value to update a single row in the table.

For example, the following UPDATE statement will update an email of an employee whose emp_id=1.

Example: Update a Row

UPDATE employee
SET email = '[email protected]'
WHERE emp_id = 1;

In the above example, the UPDATE employee indicates that we want to update data in the employee table, SET email = '[email protected]' specifies that the email column should be updated to '[email protected]'. The WHERE emp_id = 1 will update a row where emp_id value is 1, thereby it limits the update operation to only one row.

The following display the result of the above query in pgAdmin:

The UPDATE statement returns UPDATE 1 which indicates that it has updated 1 row.

Let's verify updated data using the SELECT query shown below.

WARNING: If you don't specify the WHERE clause, then it will update the email column value in all the rows.

Example: Update All Rows

UPDATE employee
SET email = '[email protected]';

RETURNING Clause with UPDATE Statement

The RETURNING clause returns the updated rows or column values. If you specify RETURNING * then it will return all the updated rows and if you specify RETURNING column_name then it will return values of the specified columns. You can specify multiple columns separated with a comma.

The following UPDATE statement returns all the updated rows:

Example: RETURNING *

UPDATE employee
SET birthdate = NULL,
    salary = 10000,
    last_name = 'Blamire'
WHERE emp_id = 4
RETURNING *;

As you can see, birthdate is set to NULL, salary is set to 10000, and the last_name is changed to "Blamire" where emp_id = 4. The order of the columns is not important with the SET clause. The following displays the result of the above query in pgAdmin:

The RETURNING clause can used with one or more columns. The following UPDATE statement returns multiple columns:

Example: RETURNING Columns

UPDATE employee
SET last_name = 'Blamire',
	birthdate = NULL,
	salary = 10000
WHERE emp_id = 4
RETURNING first_name, last_name, salary;

The RETURNING clause can use any columns of the table, not necessarily the column used in the SET clause e.g. the first_name column was not updated but we can still take it with the RETURNING clause. The above query will display the following result in pgAdmin:

Update Join

PostgreSQL supports updating data in one table based on values in another table using the UPDATE join clause.

Example:

UPDATE <table_1>
SET <column1> = <value1>
FROM <table_2>
WHERE <join_conditions_table_1_table_2>;

In the above syntax, to join another table in the UPDATE statement, specify FROM clause with the second table and provide join conditions in the WHERE clause. The FROM clause must be there just after the SET clause.

For every row for table_1, the UPDATE statement joins with rows of table_2. For all matching rows, the UPDATE statement updates the value of column1 with a new value specified in <value1>.

Consider we have department (parent table) and employee (child table) as bellow

Now we want to increase the salary of all employees in the IT department with dept_name as 'IT' to 10% of their existing salary, we can do so by following the UPDATE join statement.

Example:

UPDATE Employee emp
SET SALARY = SALARY + (SALARY * 0.10)
FROM Department dept
WHERE emp.dept_id = dept.dept_id
AND dept.dept_name = 'IT';

When the above UPDATE statement is executed, it shows 2 rows updated. For every row of the Employee table, it joins the Department table using dept_id as the matching column and department name as IT and increases the salary of those employees by 10%.

Let's select data from the Employee table and validate how salary is updated. Note that, there are two employees with emp_id as 3 and 6 belonging to the IT department with dept_id as 2.