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.

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.