主要功能
提供有条件地更新和插入数据到数据库表中
如果该行存在,执行一个UPDATE操作,如果是一个新行,执行INSERT操作
— 避免了分开更新
— 提高性能并易于使用
— 在数据仓库应用中十分有用
MERGE语句的语法如下:
- MERGE [hint] INTO [schema .] table [t_alias] USING [schema .]
-
- { table | view | subquery } [t_alias] ON ( condition )
-
- WHEN MATCHED THEN merge_update_clause
-
- WHEN NOT MATCHED THEN merge_insert_clause;
MERGE例子
- MERGE INTO copy_emp c
- USING employees e
- ON (c.employee_id=e.employee_id)
- WHEN MATCHED THEN
- UPDATE SET
- c.first_name=e.first_name,
- c.last_name=e.last_name,
- c.department_id=e.department_id
- WHEN NOT MATCHED THEN
- INSERT VALUES(e.employee_id,e.first_name,e.last_name,
- e.email,e.phone_number,e.hire_date,e.job_id,
- e.salary,e.commission_pct,e.manager_id,
- e.departmetn_id);
注意:
on子句的使用的字段不能够用于update,即Oracle不允许更新用于连接的列。
- MERGE INTO MN A
- USING MM B
- ON(A.ID=B.ID)
- WHEN MATCHED THEN
- UPDATE SET A.NAME = B.NAME
- WHEN NOT MATCHED THEN
- INSERT VALUES(B.ID, B.NAME);
- ON(A.ID=B.ID)
- MERGE INTO TEST T USING TEST1
- ON T1.NAME = T.NAME
- WHEN NOT MATCHED THEN
- INSERT
- (T.ID)
- values(
- sys_guid()
- );