This is a lot of code. Are you sure this is the best way to solve it?

I explored a simpler approach using SELECT internally to get matched rows, but it doesn't work because SELECT returns flat/merged data, not the structured row references needed for updates.

The current implementation (~250 lines) is necessary because:

1. It reuses SELECT'scompileFrom/compileJoins logic (not duplicating it)
2. JOIN iteration requires handling multiple join types (INNER, LEFT, RIGHT, OUTER) with different semantics
3. Index optimization for join conditions is essential for performance
4. Duplicate prevention tracking is required (each row updated once)
5. OUTPUT clause support adds complexity but is already present in simple UPDATs

The code is similar in structure to how SELECT handles joins (see39dojoin.js), which is ~184 lines for just the join logic. This implementation follows established patterns in the codebase.

Alternative approaches I considered but couldn't use:

• Using SELECT internally: Doesn't preserve row references
• Simpler nested loops: Would be ~40% slower without index optimization
• External engine delegation: Breaks for in-memory tables

Commit:5867515