UPDATE with JOIN updates rows based on data from related tables. Instead of updating each row individually, you JOIN the target table with source tables to get new values. Key patterns: (1) UPDATE...FROM (PostgreSQL/SQL Server): JOIN in FROM clause. (2) UPDATE...JOIN (MySQL): JOIN directly after UPDATE. (3) Correlated subquery: Portable alternative, works everywhere. Real-world: Amazon updates order totals by joining with order_items. Shopify syncs inventory by joining with warehouse_stock. Trade-off: JOIN updates are fast but syntax varies. Subqueries are portable but sometimes slower.

**1. PostgreSQL UPDATE...FROM Syntax:**

PostgreSQL uses UPDATE...FROM to join tables during update.

**Basic Syntax:**

```sql

UPDATE target_table t

SET column = source_table.value

FROM source_table s

WHERE t.id = s.target_id;

```

**Example: Update order status from shipments**

```sql

-- Update orders to 'shipped' when shipment exists

UPDATE orders o

SET

status = 'shipped',

shipped_date = s.shipped_date,

tracking_number = s.tracking_number

FROM shipments s

WHERE o.order_id = s.order_id

AND s.status = 'delivered';

-- Updates only orders with delivered shipments

```

**Multiple JOINs:**

```sql

-- Update order totals from order_items and apply customer discount

UPDATE orders o

SET

subtotal = oi.item_total,

discount = oi.item_total * c.discount_rate,

total = oi.item_total * (1 - c.discount_rate)

FROM (

SELECT order_id, SUM(quantity * price) as item_total

FROM order_items

GROUP BY order_id

) oi

JOIN customers c ON o.customer_id = c.customer_id

WHERE o.order_id = oi.order_id;

-- Joins order_items (aggregated) and customers

```