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:
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:
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:
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.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4