UPDATE with JOIN performance: (1) Index join columns: 10-100x faster with indexes on foreign keys and primary keys. (2) Use WHERE to limit scope: Update only needed rows, avoid full table scans. (3) Batch large updates: 1000-5000 rows per transaction, prevents long locks and huge transaction logs. (4) Test on subset first: Run on 100 rows before millions, verify logic is correct. (5) Monitor progress: Log batch completion, estimate time remaining. (6) Avoid triggers: Disable triggers during bulk updates if possible, re-enable after. (7) Use transactions: Wrap batches in BEGIN/COMMIT, allows rollback on error. Real-world: Amazon updates millions of order totals daily using batched UPDATE...FROM. Shopify syncs 1M+ products every 5 minutes using UPDATE...JOIN. Netflix updates billions of viewing stats using batched correlated subqueries.

Index join columns: 10-100x faster with indexes (enables index seek instead of table scan)

Use WHERE early: Filter rows before JOIN, reduces data processed

Batch updates: 1000-5000 rows per transaction, prevents long locks

Avoid triggers: Disable triggers during bulk updates if possible, re-enable after

Use covering indexes: Include all columns needed in index for index-only scans

Monitor locks: Check for blocking queries, kill long-running updates if needed

Test query plan: Use EXPLAIN to verify indexes are used, optimize if needed

Parallel updates: Split by ID range, run multiple batches in parallel (advanced)

Wrong database syntax: PostgreSQL UPDATE...FROM vs MySQL UPDATE...JOIN - syntax varies

Missing WHERE clause: Updates all rows in table, very dangerous

No backup: Cannot recover if update goes wrong, always backup first

Not indexing join columns: 100x slower without indexes, causes full table scans

Huge transactions: Updating millions of rows in 1 transaction locks table too long

Not testing first: Run on production without testing, causes data corruption

Forgetting WHERE EXISTS: Correlated subquery sets NULL for non-matching rows