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