MERGE 语句的示例

在此部分中的示例包括在结合的结果集上展现结合条件和各种 DML 操作的 MERGE 语句。

示例

下列 MERGE 语句包括 Update 和 Insert 子句，并使用相等谓词作为结合条件：

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);

下一示例在 ON 子句中指定多个谓词：

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);

下列 MERGE 语句执行 Update 结合，不带 Insert 子句：

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 ;

下列 MERGE 语句仅在结合条件之后包括 Delete 子句：

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

下一 MERGE 示例仅包括 Insert 子句：

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 和 WHEN NOT MATCHED 规范可以任何顺序出现：

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 ;

下列 MERGE 指定在 USING 子句中查询定义的派生的表为其源：

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 ;