Understanding PostgreSQL Update from Select Queries

PostgreSQL Update from Select: Synchronizing Table Data

PostgreSQL allows you to update rows in a table using data retrieved from another table or query. This is achieved through the UPDATE...FROM syntax, which is useful for synchronizing data between tables or for batch updates based on specific criteria.

This guide will explore the syntax, examples, and a step-by-step explanation of how to use UPDATE FROM SELECT in PostgreSQL effectively.

Syntax:

UPDATE target_table
SET column1 = subquery.column1,
    column2 = subquery.column2
FROM (
    SELECT source_column1, source_column2
    FROM source_table
    WHERE conditions
) AS subquery
WHERE target_table.matching_column = subquery.matching_column;

Examples and Code

1. Basic Update from Select

Code:

-- Create a sample target table
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT,
    department_id INT,
    salary NUMERIC
);

-- Insert sample data into employees
INSERT INTO employees (name, department_id, salary)
VALUES 
('Alice', 1, 50000),
('Bob', 2, 55000),
('Charlie', 3, 60000);

-- Create a source table with updated data
CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    department_name TEXT,
    bonus_percent NUMERIC
);

-- Insert sample data into departments
INSERT INTO departments (id, department_name, bonus_percent)
VALUES 
(1, 'HR', 0.10),
(2, 'Engineering', 0.15),
(3, 'Sales', 0.12);

-- Update employees salary based on bonus percentage from departments
UPDATE employees
SET salary = salary + (salary * departments.bonus_percent)
FROM departments
WHERE employees.department_id = departments.id;

Explanation:

• The employees table is updated.
• The salary column in employees is adjusted using the bonus_percent from the departments table.
• Rows in employees are matched with rows in departments based on department_id.

2. Using a Subquery

Code:

-- Adjust salary using a subquery
UPDATE employees
SET salary = salary + (
    SELECT salary * bonus_percent 
    FROM departments 
    WHERE employees.department_id = departments.id
);

Explanation:

• Instead of using FROM, a subquery directly retrieves the bonus_percent for matching rows.

3. Partial Update with Condition

Code:

-- Only update employees in the Engineering department
UPDATE employees
SET salary = salary + (salary * departments.bonus_percent)
FROM departments	
WHERE employees.department_id = departments.id
AND departments.department_name = 'Engineering';

Explanation:

• Adds a condition to update only employees in the "Engineering" department.

Explanation

1. Purpose of UPDATE FROM SELECT:
This is particularly useful when data in one table depends on related data from another table. It simplifies complex updates that would otherwise require multiple steps.

2. Key Components:

• Target Table: The table being updated.
• Source Table/Query: The data source for the updates.
• Matching Criteria: A WHERE clause to ensure correct row matching.

3. Performance Considerations:

• Indexing the matching columns in both tables can significantly improve performance.
• Use EXPLAIN to analyze query execution plans for optimization.

4. Error Handling:
Ensure the subquery or source table does not produce duplicate rows for matching conditions to avoid errors./p>

Use Cases

• Synchronizing salary updates in an HR database based on department policies.
• Updating inventory levels based on transactions from another table.
• Adjusting customer account balances based on financial records.

All PostgreSQL Questions, Answers, and Code Snippets Collection.