merge 语句用法

create table TEST_EMPLOYEES(ID NUMBER,NAME NVARCHAR2(50),SALARY NUMBER);   
insert into TEST_EMPLOYEES (ID, NAME, SALARY) values (1, '张三', 8000);   
insert into TEST_EMPLOYEES (ID, NAME, SALARY) values (2, '李四', 7000);   
insert into TEST_EMPLOYEES (ID, NAME, SALARY) values (3, '王五', 9000);   
create table TEST_EMPLOYEES2(ID NUMBER,NAME NVARCHAR2(50),SALARY NUMBER);   
insert into TEST_EMPLOYEES2 (ID, NAME, SALARY) values (1, '张三', 8000);   
insert into TEST_EMPLOYEES2 (ID, NAME, SALARY) values (2, '李四', 17000);   
insert into TEST_EMPLOYEES2 (ID, NAME, SALARY) values (3, '王五', 19000);

 merge语句的语法如下：

merge <hint>
into <table_name>
using <table_view_or_query>
on (<condition>)
when matched then <update_clause>
delete <where_clause>
when not matched then <insert_clause>
[log errors <log_errors_clause> <reject limit < integer | unlimited > ]

 

具体用法如下：

 

merge into test_employees2 a
using (select id, name, salary, dept from test_employees) b
on (a.id = b.id)
when matched then
  update set a.name = b.name, a.salary = b.salary 
  delete where (a.id = 2)
when not matched then
  insert (id, name, salary, dept) values (b.id, b.name, b.salary, b.dept);