RBN-INFO-TECH : Merge Satement In Oracle

Oracle introduces the new MERGE SQL command. The much more elegant way to insert or update data with Plsql is to use Oracles merge command.It is a DML command that enables us to optionally update or insert data into a target table, depending on whether matching already existing records. In oracle 9i, we would have to code this scenario either in separate bulk SQL statements or in PL/SQL. We will compare MERGE to these methods later in this article.

Syntax

Merge into destination_table X using source_table Y  on (X.unique_id = Y.unique_id)  when matched then    Update  set X.name = Y.name,      X.rollno = Y.rollno,      X.address = Y.address  when not matched then  insert (X.contact_id,     X.date_of_birth,     X.full_name,     X.unique_id)  values ('16-mar-1988',     Y.DoB,     Y.full_name,     Y.unique_id);

Example-:

We can now see an example of the MERGE statement. 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.
SQL> SELECT COUNT(*) FROM target_table;
    COUNT(*) ----------      43485  1 row selected. 
 SQL> 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 );
  95865 rows merged.
Note the following clauses -:
MERGE (line 1)   : as stated previously, this is now the 4th DML statement in Oracle. Any hints we might wish to add directly follow this keyword (i.e. MERGE /*+ HINT */);
INTO (line 2)      : this is how we specify the target for the MERGE. The target must be either a table or an updateable view (an in-line view cannot be used here);
USING (line 3)   : the USING clause represents the source dataset for the MERGE. This can be a single table (as in our example) or an in-line view;
ON () (line 4)    : the ON clause is where we supply the join between the source dataset and target table. Note that the join conditions must be in parentheses;
WHEN MATCHED (line 5) : this clause is where we instruct Oracle on what to do when we already have a matching record in the target table (i.e. there is a join between the source and target datasets). We obviously want an UPDATE in this case. One of the restrictions of this clause is that we cannot update any of the columns used in the ON clause (though of course we don't need to as they already match). Any attempt to include a join column will raise an unintuitive invalid identifier exception .  and
WHEN NOT MATCHED (line 10) : this clause is where we INSERT records for which there is no current match.
Note that sqlplus reports the number of rows merged. This includes both the updates and inserts. Oracle treats MERGE as a MERGE and not an UPDATE+INSERT statement. The same is true of SQL%ROWCOUNT in PL/SQL.
RBN-INFO-TECH