MERGE 2 INTO target_table tgt 3 USING source_table src 4 ON ( src.object_id = tgt.object_id ) 5 WHEN MATCHED 6 THEN 7 UPDATE 8 SET tgt.object_type = src.object_type 9 , tgt.object_name = src.object_name 10 WHEN NOT MATCHED 11 THEN 12 INSERT ( tgt.object_id 13 , tgt.object_name 14 , tgt.object_type ) 15 VALUES ( src.object_id 16 , src.object_name 17 , src.object_type ); This simply means that a single target row might be updated multiple times, which could give rise to some data quality issues (for example, if compound arithmetic such as summing is included in the update of some columns).MERGE can be used with in PL/SQL (even row-by-row) but should generally be considered a set operation.We can see that this is the same as the MERGE count.MERGE is useful for combining larger source and target datasets, particularly for slowly-changing dimensions in data warehouses.
We will start by creating the source table as a selection of ALL_OBJECTS data. In the following example, we will merge the source table into the target table.We will capture a count of the target table rows before and after the merge.MERGE 2 INTO target_table tgt 3 USING source_table src 4 ON ( src.object_id = tgt.object_id ) 5 WHEN MATCHED 6 THEN 7 UPDATE 8 SET tgt.object_name = src.object_name 9 , tgt.object_type = src.object_type 10 WHEN NOT MATCHED 11 THEN 12 INSERT ( tgt.object_id 13 , tgt.object_name 14 , tgt.object_type ) 15 VALUES ( src.object_id 16 , src.object_name 17 , src.object_type ); Note that sqlplus reports the number of rows merged. Oracle treats MERGE as a MERGE and not an UPDATE INSERT statement. As a rough sanity check, we can report the record count in the target table following the MERGE.Without the outer join, Oracle would need to implement a "two-pass" solution such as we might code ourselves with a separate INSERT and UPDATE statement.Major performance gains will be achieved by tuning the source-target join (for example, using indexes, hints, partitioning etc) or by tuning the USING clause if it is a complex in-line view.
This article introduces the new MERGE SQL command (sometimes referred to as "UPSERT").