Examples of MERGE Statements

Examples in this section include MERGE statements that illustrate join conditions and various DML operations on the result set of the join.

Examples

The following MERGE statement includes the Update and Insert clauses, and uses an equality predicate as the join condition:

MERGE INTO customer c
   USING ext_customer e
   ON c.customer_num=e.customer_num
WHEN MATCHED THEN
   UPDATE SET c.fname = e.fname,
           c.lname = e.lname,
           c.company = e.company,
           c.address1 = e.address1,
           c.address2 = e.address2,
           c.city = e.city,
           c.state = e.state,
           c.zipcode = e.zipcode,
           c.phone = e.phone
WHEN NOT MATCHED THEN
   INSERT (c.fname, c.lname, c.company, c.address1, c.address2, 
           c.city, c.state, c.zipcode, c.phone)
       VALUES
          (e.fname, e.lname, e.company, e.address1, e.address2, 
           e.city, e.state, e.zipcode, e.phone);

The next example specifies multiple predicates in the ON clause:

MERGE INTO customer c
   USING ext_customer e
   ON c.customer_num=e.customer_num 
      AND c.fname=e.fname AND c.lname=e.lname
WHEN MATCHED THEN
   UPDATE SET c.fname = e.fname,
           c.lname = e.lname,
           c.company = e.company,
           c.address1 = e.address1,
           c.address2 = e.address2,
           c.city = e.city,
           c.state = e.state,
           c.zipcode = e.zipcode,
           c.phone = e.phone
WHEN NOT MATCHED THEN
   INSERT
        (c.fname, c.lname, c.company, c.address1, c.address2, 
         c.city, c.state, c.zipcode, c.phone)
        VALUES
        (e.fname, e.lname, e.company, e.address1, e.address2, 
         e.city, e.state, e.zipcode, e.phone);

The following MERGE statement performs an Update join, with no Insert clause:

MERGE INTO customer c
USING ext_customer e
ON c.customer_num=e.customer_num
WHEN MATCHED THEN
UPDATE SET c.fname = e.fname,
           c.lname = e.lname,
           c.company = e.company,
           c.address1 = e.address1,
           c.address2 = e.address2,
           c.city = e.city,
           c.state = e.state,
           c.zipcode = e.zipcode,
           c.phone = e.phone ;

The following MERGE statement includes only the Delete clause after the join condition:

MERGE INTO customer c
   USING ext_customer e
      ON c.customer_num=e.customer_num
      WHEN MATCHED THEN
         DELETE ;

The next MERGE example includes only the Insert clause:

MERGE INTO customer c
USING ext_customer e
ON c.customer_num=e.customer_num AND c.fname=e.fname 
   AND c.lname=e.lname
WHEN NOT MATCHED THEN
   INSERT
      (c.fname, c.lname, c.company, c.address1, c.address2, 
       c.city, c.state, c.zipcode, c.phone)
       VALUES
        (e.fname, e.lname, e.company, e.address1, e.address2, 
         e.city, e.state, e.zipcode, e.phone);

The next example illustrates that the WHEN MATCHED and WHEN NOT MATCHED specifications can appear in any order:

MERGE INTO customer c
   USING ext_customer e
   ON c.customer_num=e.customer_num AND c.fname=e.fname AND c.lname=e.lname
WHEN NOT MATCHED THEN 
     INSERT
        (c.fname, c.lname, c.company, c.address1, c.address2, 
         c.city, c.state, c.zipcode, c.phone)
     VALUES
        (e.fname, e.lname, e.company, e.address1, e.address2, 
         e.city, e.state, e.zipcode, e.phone)
WHEN MATCHED THEN UPDATE
       SET c.fname = e.fname,
           c.lname = e.lname,
           c.company = e.company,
           c.address1 = e.address1,
           c.address2 = e.address2,
           c.city = e.city,
           c.state = e.state,
           c.zipcode = e.zipcode,
           c.phone = e.phone ;

The following MERGE statement specifies as its source a derived table that the query in the USING clause defines:

MERGE INTO customer c
   USING (SELECT * from ext_customer e1, orders e2 
          WHERE e1.customer_num=e2.customer_num ) e
   ON c.customer_num=e.customer_num AND c.fname=e.fname 
      AND c.lname=e.lname
   WHEN NOT MATCHED THEN
      INSERT (c.fname, c.lname, c.company, c.address1, c.address2,
              c.city, c.state, c.zipcode, c.phone)
      VALUES (e.fname, e.lname, e.company, e.address1, e.address2,
              e.city, e.state, e.zipcode, e.phone)
   WHEN MATCHED THEN
      UPDATE SET c.fname = e.fname,
           c.lname = e.lname,
           c.company = e.company,
           c.address1 = e.address1,
           c.address2 = e.address2,
           c.city = e.city,
           c.state = e.state,
           c.zipcode = e.zipcode,
           c.phone = e.phone ;