Update statement with inner join on Oracle

Spice up your updates with alternatives

Sometimes, donning an updatable inline view or invoking the MERGE statement could save the day. Perfect for cases needing multiple column updates concurrently or complex scenarios requiring an insert if the row just doesn't exist yet:

Updatable inline view in all its glory:

UPDATE (SELECT t1.column AS old_value, t2.updated_value AS new_value 
        FROM table1 t1
        INNER JOIN table2 t2 ON t1.id = t2.id) table_view -- Provide an alias for readability's millennial cousin, scannability.
SET table_view.old_value = table_view.new_value; -- Out with the old, in with the new.


This updates table1.column with table2.updated_value while the subquery performs an inner join. Refer to this oracle guide for comprehensive details on possible updates via inline views.

Use the MERGE statement to literally merge:

MERGE INTO table1 trg
USING (SELECT id, updated_value FROM table2) src -- Keep it DRY, Don't Repeat Yourself.
ON (trg.id = src.id)
WHEN MATCHED THEN 
  UPDATE SET trg.column = src.updated_value; -- When stars align, updates happen.


The MERGE conjoins insert and update operations, orchestrating the pairing of target trg and source src. If rows match according to the ON clause, the conditioned UPDATE takes effect.

A word of caution: ORA-38104 is not a model number, it's an error you'll meet if you update the join column.

Performance tip: Indexed columns are speedboats, use them in joins for fast and furious results.

Stepping up the game for complex updates with joins

In scenarios more complex than a Christopher Nolan movie, where there are multiple tables and conditions, strategic use of aliases and conditional WHERE clauses can guide you through a clean and safe update process:

Complex update example:

UPDATE (SELECT t1.column AS column_to_update, t2.updated_value
        FROM table1 t1
        INNER JOIN table2 t2 ON t1.id = t2.id -- Establish the relationship. It’s complicated.
        WHERE t2.condition = 'specific_value') alias_view -- Yes, that's an alias for an inline view.
SET alias_view.column_to_update = alias_view.updated_value; -- Say goodbye to your past, column!


The alias_view table alias, made easy for you to traverse through the complex join. When it gets tangled, remember to name the columns and tables distinctly to stave off ambiguities.

Key considerations for integrity and performance

Thinking about data integrity and performance are not side gigs when handling updates with joins:

• Avoid Cartesian joins: Remember to ensure proper join conditions to prevent inadvertent errors.
• Index primary and foreign keys: This is an express route for enhanced join performance.
• Validate data: Arm your queries with EXISTS or IN clauses to validate data before updating.

These nuggets of wisdom will take you to a smoother, safer, and more efficient update process that your future self will thank you for.