Updating table rows in PostgreSQL with Subqueries

Updating Table Rows in PostgreSQL Using a Subquery

Updating rows in PostgreSQL using a subquery is helpful when you need to update data in one table based on values from another table or condition. A subquery in an UPDATE statement allows you to select data from other tables or from the same table to determine the new values for the update.

Description

The UPDATE command with a subquery enables you to modify specific rows in a table by setting column values based on results from a nested query. This is particularly useful when updating rows conditionally, using related data in another table or a complex calculation.

Syntax:

UPDATE table_name
SET column_name = (SELECT value_column FROM other_table WHERE condition)
WHERE another_condition;

Command explanation:

• table_name: The table to update.
• column_name: The column in table_name to be updated.
• other_table: The table from which data is selected in the subquery.
• value_column: The column in other_table that provides the new values.
• condition: Condition to match rows in the subquery.
• another_condition: Optional condition to limit rows in table_name for the update.

Example: Updating Rows with a Subquery

Suppose we have two tables: employees and departments. The employees table has a salary column, and the departments table has an average_salary column for each department. We want to update employees’ salaries based on their department's average salary.

Example Query

Code:

-- Update employees' salary based on their department's average salary
UPDATE employees
SET salary = (SELECT average_salary 
              FROM departments 
              WHERE departments.id = employees.department_id)
WHERE employees.salary < (SELECT average_salary 
                          FROM departments 
                          WHERE departments.id = employees.department_id);

Explanation:

• UPDATE employees: Specifies the employees table for updating.
• SET salary =: Defines the new value for the salary column.
• The subquery (SELECT average_salary FROM departments WHERE departments.id = employees.department_id) retrieves the average salary for each employee's department.
• The WHERE clause restricts the update to only those employees whose current salary is below the department's average.

Example: Update employee salaries if below department average

Code:

-- Update the employees table
UPDATE employees
-- Set the salary to the department's average salary
SET salary = (
    -- Subquery to select the average salary for the employee's department
    SELECT average_salary 
    FROM departments 
    WHERE departments.id = employees.department_id
)
-- Only update rows where the employee's salary is below the department average
WHERE employees.salary < (
    -- Subquery to get the average salary again for comparison
    SELECT average_salary 
    FROM departments 
    WHERE departments.id = employees.department_id
);

Explanation:

• Subquery for Update: The first subquery retrieves the average salary for each employee's department and sets it as the new salary.
• Conditional Update: The WHERE clause ensures only employees with salaries below their department’s average salary are updated.

Note: If subqueries are complex or involve multiple joins, test the subquery separately to ensure accuracy before using it within an UPDATE command.

All PostgreSQL Questions, Answers, and Code Snippets Collection.