How to do an update + join in PostgreSQL?

Performing UPDATE with JOIN in PostgreSQL

Learn how to efficiently update rows in one table based on data from another table using various JOIN techniques in PostgreSQL.

Updating data in a database is a common operation, and often, the values you need for the update reside in a different table. PostgreSQL provides powerful and flexible ways to perform UPDATE statements that incorporate data from other tables using JOIN clauses. This article will explore the different syntaxes and best practices for achieving this, ensuring your data remains consistent and accurate.

Understanding the Basic UPDATE Syntax

Before diving into UPDATE with JOIN, let's review the standard UPDATE statement. It allows you to modify existing records in a table. The SET clause specifies which columns to modify and their new values, while the WHERE clause filters which rows to update.

UPDATE products
SET price = 10.99
WHERE product_id = 101;

Basic UPDATE statement in PostgreSQL

UPDATE with FROM Clause (Standard PostgreSQL Approach)

PostgreSQL extends the SQL standard by allowing a FROM clause in the UPDATE statement. This is the most common and recommended way to join tables for an update operation. You specify the table to be updated, then list the tables to join with in the FROM clause, and finally define the join conditions in the WHERE clause.

flowchart TD
    A[Start Update] --> B{Identify Target Table}
    B --> C{Specify Tables in FROM Clause}
    C --> D{Define Join Conditions in WHERE}
    D --> E{Set New Values}
    E --> F[End Update]

Workflow for UPDATE with FROM clause

-- Example: Update product prices based on a category discount
UPDATE products p
SET price = p.price * (1 - c.discount_percentage)
FROM categories c
WHERE p.category_id = c.category_id
  AND c.category_name = 'Electronics';

Updating product prices using a JOIN with the categories table

In this example:

• products p is the target table being updated.
• categories c is the table joined from.
• p.category_id = c.category_id is the join condition.
• c.category_name = 'Electronics' is an additional filter to apply the discount only to specific categories.

UPDATE with Subquery (Alternative Approach)

While the FROM clause is generally preferred in PostgreSQL, you can also achieve an UPDATE with JOIN functionality using a subquery. This approach can be useful in scenarios where the logic is simpler or when you need to aggregate data from the joined table before updating.

-- Example: Update product prices based on a category's average price
UPDATE products
SET price = (SELECT AVG(p2.price)
             FROM products p2
             WHERE p2.category_id = products.category_id)
WHERE category_id IN (SELECT category_id FROM categories WHERE category_name = 'Books');

Updating product prices using a correlated subquery

In this subquery example:

• The inner subquery (SELECT AVG(p2.price) ...) calculates the average price for each category.
• The outer UPDATE statement then sets the price for products in the 'Books' category to their respective category's average price.

This method can sometimes be less performant than the FROM clause for complex joins, as the subquery might be executed for each row being updated.

Best Practices and Considerations

When performing UPDATE operations involving JOINs, keep the following in mind:

1. Use Transactions

Wrap your UPDATE statements in a transaction (BEGIN; ... COMMIT; or ROLLBACK;). This allows you to revert changes if something goes wrong.

2. Verify with SELECT

Before executing the UPDATE, run a SELECT statement with the same FROM and WHERE clauses to see exactly which rows would be affected and what the new values would be. This is a crucial verification step.

3. Index Foreign Keys

Ensure that the columns used in your JOIN conditions (especially foreign keys) are indexed. This significantly improves the performance of UPDATE statements involving joins.

4. Consider Locking

Large UPDATE operations can acquire locks on tables, potentially blocking other operations. If possible, schedule large updates during off-peak hours or break them into smaller batches.