PostgreSQL UPDATE JOIN (Actually UPDATE FROM) 2026 Guide

PostgreSQL does not have an UPDATE JOIN. It has UPDATE FROM, which is the Postgres-specific equivalent. The syntax confuses developers coming from MySQL or SQL Server because those databases use UPDATE JOIN. Once you know the difference, the Postgres version is cleaner. Below is the full 2026 syntax, performance considerations, the PostgreSQL 18 improvements that matter, and the common mistakes that produce silent data corruption.

Quick reference: UPDATE FROM in PostgreSQL

The basic UPDATE FROM pattern

The simplest case, updating one table based on data from another:

```sql
UPDATE orders o
SET status = c.tier
FROM customers c
WHERE o.customer_id = c.id;
```

Three things to notice:

1. The target table is the one in UPDATE: Aliased `o` here.

2. The source table is in FROM: Aliased `c`. This is what would be JOIN in other databases.

3. The join condition goes in WHERE: Required. Missing this produces a Cartesian product.

The result: every row in `orders` where `customer_id` matches a customer gets its status updated to that customer's tier.

Multiple source tables

You can join multiple source tables in the FROM clause:

```sql
UPDATE invoices i
SET total = i.amount + t.value
FROM taxes t, regions r
WHERE i.region_id = r.id
AND r.tax_id = t.id;
```

Both `taxes` and `regions` are sources. The join conditions in WHERE link target to source and source to source.

The mistake to avoid: listing the target table again in FROM creates a self-join with unintended behavior. Postgres warns but does not always error. Audit your WHERE clause.

UPDATE with subqueries

Sometimes a subquery is clearer than a join:

```sql
UPDATE products p
SET price = (
SELECT new_price
FROM price_updates
WHERE product_id = p.id
);
```

This works but has a quirk: if no row matches in the subquery, the column is set to NULL. Often not what you want. Add a WHERE EXISTS filter:

```sql
UPDATE products p
SET price = (
SELECT new_price
FROM price_updates
WHERE product_id = p.id
)
WHERE EXISTS (
SELECT 1
FROM price_updates
WHERE product_id = p.id
);
```

Verbose but safe.

RETURNING clause for auditing

PostgreSQL supports RETURNING on UPDATE to capture what was changed:

```sql
UPDATE orders o
SET status = 'shipped'
FROM shipments s
WHERE o.id = s.order_id
AND s.shipped_at IS NOT NULL
RETURNING o.id, o.status;
```

PostgreSQL 18 expanded RETURNING:

```sql
UPDATE orders o
SET status = 'shipped'
FROM shipments s
WHERE o.id = s.order_id
RETURNING o.id, old.status, new.status;
```

The `old.col, new.col` syntax is new in PG18. Useful for auditing what changed without separate before-and-after queries.

Performance considerations

Three things that affect UPDATE FROM performance:

1. Index the join keys: Without indexes, Postgres does sequential scans on both tables. With indexes, it uses index lookups or hash joins. Always index the columns in your WHERE clause.

2. Lock level: UPDATE takes RowExclusive on the table and FOR UPDATE on the rows being modified. Long-running UPDATEs block concurrent modifications. Plan for this.

3. Batch large updates: An UPDATE that modifies 10 million rows in a single transaction creates massive WAL volume, locks rows for a long time, and produces table bloat. Batch in 10K-50K chunks:

```sql
-- Loop pattern
DO $$
DECLARE
batch_size INT := 10000;
rows_updated INT;
BEGIN
LOOP
UPDATE orders o
SET status = c.tier
FROM customers c
WHERE o.customer_id = c.id
AND o.status IS DISTINCT FROM c.tier
AND o.id IN (
SELECT id FROM orders
WHERE status != c.tier
LIMIT batch_size
);
GET DIAGNOSTICS rows_updated = ROW_COUNT;
EXIT WHEN rows_updated < batch_size;
PERFORM pg_sleep(0.1);
END LOOP;
END $$;
```

The `IS DISTINCT FROM` check skips rows that would not change. The LIMIT batches the work. The sleep gives autovacuum time to keep up.

PostgreSQL 18 improvements that matter

PostgreSQL 18 (released September 2025) made UPDATE FROM 10-30% faster on typical OLTP workloads. Two specific improvements:

1. Hash join performance: Improved memory usage and execution speed for hash joins, which UPDATE FROM often uses.

2. Merge joins with incremental sorts: Lower cost on partially-sorted inputs. Useful for backfill jobs that operate on data already mostly in order.

If you run heavy migration or backfill jobs, test PostgreSQL 18 specifically for UPDATE FROM performance. Real-world lift is meaningful.

Common UPDATE FROM mistakes

Five mistakes that produce silent data corruption or performance issues:

1. Missing WHERE clause: Cartesian product. Updates every row to whatever the source returns last. Catastrophic.

```sql
-- WRONG: missing WHERE
UPDATE orders SET status = c.tier
FROM customers c;
```

This updates every order with the status of one arbitrary customer. Lock disabled production systems.

2. Source has duplicates that match target: Postgres silently picks one row arbitrarily. Result: non-deterministic update.

```sql
-- DANGEROUS if customers has duplicate ids
UPDATE orders o SET status = c.tier
FROM customers c
WHERE o.customer_id = c.id;
```

Use DISTINCT or a subquery with ORDER BY plus LIMIT 1 to make the source deterministic.

3. Listing the target table in FROM: Creates an unintended self-join.

```sql
-- WRONG: orders is both target and source
UPDATE orders o SET status = c.tier
FROM orders o2
WHERE o.id = o2.id;
```

Most modern Postgres versions warn but this still happens in legacy code.

4. Updating columns that did not change: Wastes rows in WAL and triggers cascading updates. Use `IS DISTINCT FROM` to skip no-op updates:

```sql
UPDATE orders o
SET status = c.tier
FROM customers c
WHERE o.customer_id = c.id
AND o.status IS DISTINCT FROM c.tier;
```

5. No batching on large updates: 10 million row UPDATEs lock too long, bloat the table, and generate WAL faster than autovacuum can keep up. Batch in 10K-50K chunks.

If your team is also working on advancing your data maturity model, tighter UPDATE patterns are usually a prerequisite for trustworthy analytics on top.

What changed in 2025-2026

Three real shifts:

PostgreSQL 18 made UPDATE FROM 10-30% faster: Hash join and merge join improvements deliver real performance lift on large operations.

RETURNING `old.col, new.col` syntax became standard: PG18 added it. Useful for auditing without separate before-and-after queries.

Async I/O subsystem helps backfill jobs: Concurrent disk I/O on PG18 makes UPDATE FROM on read-heavy workloads materially faster.

FAQ

What is the PostgreSQL equivalent of UPDATE JOIN?

UPDATE FROM. PostgreSQL syntax is `UPDATE target SET col = source.col FROM source WHERE target.id = source.id`. The MySQL and SQL Server UPDATE JOIN syntax does not exist in PostgreSQL.

Why does UPDATE FROM sometimes produce non-deterministic results?

When the source table has multiple rows that match the target row, PostgreSQL picks one arbitrarily. Use DISTINCT, GROUP BY, or a subquery with ORDER BY plus LIMIT 1 to make the source deterministic.

How do I batch a large UPDATE in PostgreSQL?

Loop in chunks of 10K-50K rows with a DO block. Use `IS DISTINCT FROM` to skip no-op updates. Add a brief `pg_sleep` between batches to give autovacuum time. Avoids long locks, table bloat, and WAL spikes.

What is the RETURNING old.col syntax?

PostgreSQL 18 added `RETURNING old.col, new.col` to capture before-and-after values in a single statement. Useful for auditing UPDATEs without separate queries before the change.

Should I upgrade to PostgreSQL 18 for UPDATE FROM performance?

Yes if you run heavy migration or backfill jobs. PG18 delivers 10-30% faster UPDATE FROM on typical OLTP workloads. Test in staging first. Hash join, merge join, and async I/O improvements all help.

Stop overpaying for AI tools you barely use. See how Dupple X helps your team adopt AI without the bloat.