Update a Table with Data from Another Table

sql

join

subquery

update

byAlex Kataev·Mar 9, 2025

The UPDATE JOIN command allows you to alter a table using another table's data, a usage as smooth as a hot knife through butter:

UPDATE target
SET target.column = source.column
FROM source
WHERE target.id = source.id;

Alter target to your target table, column for the field you need to modify, and source for the table containing those fresh data, ensuring id matches the records in both. This one-liner aligns the column in your target table using corresponding source values—like two puzzle pieces fitting together!

Synchronous IDs: Correlation is Everything

Achieve precise updates with a shared id. This bond ensures our updates are reliable and accurate:

UPDATE target
SET target.name = (SELECT source.name
                   FROM source
                   WHERE target.id = source.id), -- id-pairing, ID-dancing!
    target.desc = (SELECT source.desc
                   FROM source
                   WHERE target.id = source.id)
WHERE EXISTS (SELECT 1
              FROM source
              WHERE target.id = source.id); -- Existential crisis solved!

The EXISTS clause secures the update, avoiding any rows without a corresponding id in the source table. Less careless updating, more careful correlating!

Handling Complexities: Multi-table Updates

For Oracle SQL users, join in the efficiency party with multi-table updates:

UPDATE (SELECT target.name as old_name, source.name as new_name
        FROM target
        JOIN source ON target.id = source.id) -- For once, joining is productive
SET old_name = new_name; --identity theft is not a joke, Jim!

No need to specify the join condition multiple times. Result? Purer, simpler, code.

Atomic updates: Harness the Power of Merge

Meet MERGE, SQL's superhero for complex table updates:

MERGE INTO target USING source
ON (target.id = source.id)
WHEN MATCHED THEN 
    UPDATE SET target.name = source.name, target.desc = source.desc -- Fun parties always have a good match!
WHEN NOT MATCHED THEN 
    INSERT (id, name, desc)
    VALUES (source.id, source.name, source.desc); -- Hello there, newcomers!

New or existent—MERGE has them covered! It updates existing records and graciously allows new ones to join.

Potential Pitfalls: Sidestep the ORA-01427

Who likes errors? Nobody. When your subquery returns multiple rows, the ORA-01427 crashes your party:

UPDATE target 
SET target.column = (SELECT source.column FROM source WHERE source.id = target.id); -- Uh oh, too many participants.

An ORA-01427 error is no joke. Keep those subqueries in check—remember, one is the loneliest (but cleanest) number!

Composite Keys: Updating Across Multiple Columns

Working with composite keys? Work IN with tuples to update across multiple columns:

UPDATE target
SET target.column = source.column
WHERE (target.id, target.another_column) IN (SELECT source.id, source.another_column
                                             FROM source); -- Futuristic key cloning!

Curate your Targets: WHERE and IN

For cherry-picked updates, rally WHERE with the IN clause to target specific rows:

UPDATE target
SET target.column = source.column
WHERE target.id IN (SELECT source.id
                    FROM source
                    WHERE source.some_condition = true); -- You have no power here, unwanted rows!