PostgreSQL UPDATE Query

Introduction to PostgreSQL Update Query

PostgreSQL update query is used to change the information stored in a database table. It's like editing a specific piece of data in a digital spreadsheet. The PostgreSQL update query is a useful tool to keep the data accurate and up-to-date.

For example, imagine you have a table of employees, and you want to change the salary of a specific employee. The update query allows you to do just that. You specify which table you want to update, which column you want to change (like the salary column), and what new value you want to set (the updated salary).

Syntax of PostgreSQL Update Query

Now, let’s understand the syntax of the postgreSQL update query:

• table_name: The name of the table you want to update.
• column1, column2: The columns you want to modify.
• value1, value2: The new values we want to set for the corresponding columns.
• WHERE condition: An optional clause that specifies which rows should be updated. If not provided, all rows in the table will be affected.

Parameters of PostgreSQL Update Query

Let's learn about each parameter of the PostgreSQL UPDATE query:

• UPDATE: The UPDATE keyword is the starting point of the UPDATE query. It indicates that you want to modify existing data in the specified table. It is followed by the table name and the SET clause.
• Table_name: The table_name parameter is where you specify the name of the table you want to update. This is the table where the data exists, and you want to make changes to specific rows.
• Columns: Columns refer to the individual fields in the table that you want to modify. In the SET clause, you list the column names you want to update, separated by commas, and assign new values to them.
• WHERE: The WHERE keyword is an optional parameter that allows you to set conditions for the UPDATE operation. It lets you specify which rows in the table should be updated. If you omit the WHERE clause, all rows in the table will be affected by the UPDATE query.
• Condition: The condition is part of the WHERE clause and determines which rows should be updated based on specific criteria. You can use various comparison operators (e.g., =, <, >, <=, >=, <>, etc.) along with logical operators (e.g., AND, OR) to create complex conditions.

Basic Example of PostgreSQL Update Query

Let’s create a table employees to better understand the postgreSQL update query.

now, let's insert the data into the table by the below insert statement.

The table has been generated in the below format by inserting the data:

Now, If we want to update the salary of an employee with a specific ID, we would use the UPDATE query as follows:

Example:

After executing the above query, the below output will be generated:

Output:

In this example:

• UPDATE indicates we want to update data.
• employees is the name of the table we want to modify.
• SET salary = new_salary_value specifies that we want to update the salary column with a new value.
• WHERE employee_id = target_employee_id sets the condition that the update should apply only to the row with the specific "employee_id" we want to target.

The above query will update the salary column for the employee with employee_id equal to 101 to the new value of 60000.

RETURNING Clause

In PostgreSQL, the RETURNING clause is an optional part of the UPDATE query that allows you to retrieve the modified data after performing the update operation. This is particularly useful when you need to see the updated values of certain columns or the entire row after the update is executed.

The syntax of the UPDATE query with the RETURNING clause is as follows:

• table_name: The name of the table you want to update.
• column1, column2: The columns you want to modify and update with new values.
• value1, value2: The new values you want to set for the corresponding columns.
• WHERE condition: An optional clause that specifies which rows should be updated. If not provided, all rows in the table will be affected.
• RETURNING column1, column2: This part allows you to specify which columns you want to retrieve from the updated rows. It can be any combination of columns you want to see.

considering the above scenario, let's perform it on the table employees.

Example:

After executing the above query, the below output will be generated:

Output:

More Examples

Example of PostgreSQL UPDATE Table

Let’s consider the above-created employees table. Now, let's say we want to update the salary of the employee with ID 102 to $48000. We would use the following UPDATE query:

Example:

After executing the above query, the below output will be generated:

Output:

Upgrading all rows in a table using the Update command

Let’s consider the above-created employees table. We want to give all employees a salary raise of $1000.

The UPDATE query to achieve this will look like this:

Example:

After executing the above query, the below output will be generated:

Output:

In this example:

• employees is the name of the table we want to update.
• salary is the column we want to modify.
• salary + 1000 calculates the new salary for each employee by adding $1000 to their existing salary.

When this UPDATE query is executed, it will update all rows in the employees table, giving each employee a salary raise of $1000.

PostgreSQL Update Joins Command

Let’s consider the above-created employees table. Let's assume we want to update the Postgresql query of the table employees with new salaries from the salary_updates table for matching employee IDs.

Example:

After executing the above query, the below output will be generated:

Output:

In this example:

• employees AS e is an alias for the employees table.
• salary_updates AS su is an alias for the salary_updates table.
• e.employee_id = su.employee_id is the join condition, connecting the employees and salary_updates tables based on their employee_id columns.

Update command through RETURNING condition

Let’s consider the above-created employees table. Suppose we want to give a salary raise of $2000 to all employees and see the updated salary for one specific employee with ID 101.

The UPDATE command with the RETURNING clause will look like this:

Example:

After executing the above query, the below output will be generated:

Output:

In this example:

• employees is the name of the table we want to update.
• salary + 2000 calculates the new salary for each employee by adding $2000 to their existing salary.
• WHERE employee_id = 101 specifies that we want to update the salary of the employee with ID 101.
• RETURNING employee_id, first_name, last_name, salary allows us to retrieve the updated data for the specific employee with ID 101.