In PostgreSQL and TimescaleDB, you can combine the UPDATE statement with JOIN operations to update records in one table based on values from another table. This feature enables database administrators and developers to efficiently modify data across related tables in a single operation, significantly simplifying complex data maintenance tasks.
While standard UPDATE statements modify records in a single table, UPDATE with JOIN allows you to use data from related tables to determine which rows to update and what values to set. This capability is essential for maintaining data consistency and implementing business logic across relational databases.
UPDATE With JOIN Syntax
UPDATE table_name
SET column1 = new_value1,
column2 = new_value2
FROM source_table
WHERE table_name.column = source_table.column;
The PostgreSQL UPDATE with JOIN syntax differs slightly from other database systems. Instead of using a JOIN keyword, PostgreSQL uses a FROM clause to specify the table(s) from which to draw data for the update operation.
Examples
Let's look at some examples to better understand UPDATE with JOIN. For these examples, we'll use two tables from an e-commerce system: products and inventory.
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_name TEXT NOT NULL,
price DECIMAL(10, 2),
category TEXT,
last_updated TIMESTAMPTZ,
active BOOLEAN DEFAULT TRUE
);
Let's say we want to mark products as inactive if they're out of stock across all warehouses. We can use UPDATE with JOIN to identify which products need to be deactivated:
UPDATE products
SET active = false,
last_updated = CURRENT_TIMESTAMP
FROM inventory
WHERE products.product_id = inventory.product_id
AND inventory.quantity = 0;
This query would mark any product as inactive if it has at least one inventory record with zero quantity.
Important note: When using UPDATE with FROM in PostgreSQL, if multiple rows in the joined table (inventory) match a single row in the target table (products), the target row will be updated multiple times. In this example, if a product has multiple inventory records with quantity = 0, the same product will be updated once for each matching inventory row. The final result will be the same in this case, but this behavior can cause unexpected results with more complex updates.
Aggregate-based updates
A more accurate approach would be to check if the total quantity across all warehouses is zero. For this, we need a subquery with aggregation:
UPDATE products
SET active = false,
last_updated = CURRENT_TIMESTAMP
WHERE product_id IN (
SELECT product_id
FROM inventory
GROUP BY product_id
HAVING SUM(quantity) = 0
);
Price updates based on category performance
Imagine we want to adjust prices based on sales performance by category. We might have a sales table:
CREATE TABLE sales (
sale_id SERIAL PRIMARY KEY,
product_id INTEGER REFERENCES products(product_id),
quantity INTEGER,
sale_date DATE
);
Now, let's update product prices based on category performance:
UPDATE products
SET price = price * CASE
WHEN category_performance.total_sales > 50 THEN 1.05 -- 5% increase for high-performing categories
WHEN category_performance.total_sales < 20 THEN 0.95 -- 5% decrease for low-performing categories
ELSE 1.0 -- No change for average performers
END,
last_updated = CURRENT_TIMESTAMP
FROM (
SELECT p.category, SUM(s.quantity) as total_sales
FROM products p
JOIN sales s ON p.product_id = s.product_id
WHERE s.sale_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY p.category
) as category_performance
WHERE products.category = category_performance.category;
Data synchronization between systems
UPDATE with JOIN is invaluable when synchronizing data between systems. For example, updating product information from an external catalog:
-- Update products with information from external catalog
UPDATE products
SET product_name = ec.product_name,
last_updated = ec.updated_at
FROM external_catalog ec
WHERE products.product_id = ec.product_id
AND ec.updated_at > products.last_updated;
This is a perfect use case for UPDATE with JOIN, as we expect a one-to-one relationship between the products and external_catalog tables based on product_id.
Conditional updates with CASE
Combining UPDATE JOIN with CASE statements allows for sophisticated conditional logic:
UPDATE products
SET price = CASE
WHEN i.quantity < 10 THEN products.price * 1.1 -- Increase price by 10% for low stock items
WHEN i.quantity > 100 THEN products.price * 0.9 -- Discount items with high inventory
ELSE products.price -- Keep price the same for normal stock levels
END,
last_updated = CURRENT_TIMESTAMP
FROM (
SELECT product_id, SUM(quantity) as quantity
FROM inventory
GROUP BY product_id
) i
WHERE products.product_id = i.product_id;
Notice that we've used a subquery with aggregation to ensure each product is only updated once, avoiding the multiple-update issue that can occur with one-to-many relationships.
Critical Use Cases for UPDATE With JOIN
Data consistency management: Keeping related records in sync across multiple tables
Business rule implementation: Applying complex business rules that depend on data from multiple sources
Bulk data maintenance: Efficiently updating large numbers of records based on relational criteria
Data quality control: Correcting or standardizing data based on reference information
System integration: Synchronizing data between different systems or databases
Historical data processing: Updating records based on historical trends or aggregated information
Hierarchical data updates: Propagating changes up or down hierarchical data structures
Compliance and audit trail maintenance: Updating status flags based on compliance criteria
Performance Considerations and Common Hurdles
When using UPDATE with JOIN operations, keep these considerations in mind:
Multiple matches: With PostgreSQL's FROM clause, if multiple rows in the source table match a single row in the target table, the target row will be updated once for each matching source row. This is a common source of bugs.
One-to-many relationships: To avoid multiple updates when joining to tables with one-to-many relationships, use a subquery with DISTINCT or aggregate functions:
UPDATE products
SET active = false,
last_updated = CURRENT_TIMESTAMP
WHERE product_id IN (
SELECT DISTINCT product_id
FROM inventory
WHERE quantity = 0
);
Indexing: Ensure that join columns are properly indexed for optimal performance
Transaction size: Large updates may lock tables for extended periods; consider batching updates
Concurrency: Be aware of how concurrent operations may interact with your updates
RETURNING clause: Use the RETURNING clause to verify which rows were updated:
UPDATE products
SET active = false,
last_updated = CURRENT_TIMESTAMP
FROM inventory
WHERE products.product_id = inventory.product_id
AND inventory.quantity = 0
RETURNING products.product_id, products.product_name, products.active;
Execution plan analysis: Use EXPLAIN ANALYZE to understand and optimize complex UPDATE JOIN operations:
EXPLAIN ANALYZE
UPDATE products
SET active = false
FROM inventory
WHERE products.product_id = inventory.product_id
AND inventory.quantity = 0;
Scale PostgreSQL via Partitioning: A Dev’s Intro to Hypertables
Boosting Postgres INSERT Performance by 2x With UNNEST