MERGE 语句的示例分享-GBASE

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,

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