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.
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
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
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
SET email = '[email protected]' specifies that the email column should be updated to
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.
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:
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
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:
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:
PostgreSQL supports updating data in one table based on values in another table using the UPDATE join clause.
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
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.
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.