One of the first I notice when I started using the Oracle database was how difficult and slow it is to update a table using the data in another table compare to Microsoft SQL Server. After some years of working around this challenge, I find a solution, MERGE statement.

The MERGE statement is not designed just to update a table from another table, but I find the update when matched part very useful for this challenge I have been facing and I decided to share it with you. From my experience, the merge statement could be up to 10x faster then update query from another table.

The Oracle MERGE statement selects data from source table or query and updates, insert or delete it from/into a target table. The MERGE statement requires you to specify the join condition between the source table/query and the target table to determine whether the data in the source is found in the target table or not. When the data is found (WHEN MATCHED THEN), and update or/and delete operation can be performed on the target table, while an insert from the source to the target table can be performed when the data is not found(WHEN NOT MATCHED THEN). Below is the syntax for a complete MERGE statement:

MERGE INTO target_table a
USING source_table b
ON (a.id=b.id) --join condition
    WHEN MATCHED THEN
        UPDATE SET col1 = value1, col2 = value2,...
        [WHERE <update_condition>]
        [DELETE WHERE <delete_condition>] 
    WHEN NOT MATCHED THEN
        INSERT (col1,col2,...)
        values(value1,value2,...)
        WHERE <insert_condition>;

Since we are most concerned with updating the target table from the source table or query in this article, our MERGE query will not be including the WHEN NOT MATCHED THEN part of the MERGE statement.

MERGE INTO target_table a
USING source_table b
ON (a.p_id=b.id)
    WHEN MATCHED THEN
        UPDATE SET a.parent_name = b.name
        WHERE a.parent_name is null;
COMMIT;

The destination source can either be a table name or a script

MERGE INTO target_table a
USING (select id,parent_name from source_table) b
ON (a.p_id=b.id)
    WHEN MATCHED THEN
        UPDATE SET a.parent_name = b.name
        WHERE a.parent_name is null;
COMMIT;

Originally posted on medium

References

https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606

https://oracle-base.com/articles/9i/merge-statement

https://www.oracletutorial.com/oracle-basics/oracle-merge/