Easiest and fast way to update an Oracle table using the data in another table
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:
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.
The destination source can either be a table name or a script
References
