语法:
merge into table_name talbe_alias //目标表
using(table | view | sub_query) alias //源表
on(join condition) //合并数据的条件
when matched then //如果条件成立则更新
update set col1=col_val, col2=co2_val...
when not matched then //如果条件不成立则插入
insert (column_list) values(column_values);
示例:
//新建一张表
create table test1(
eid number(10),
name varchar2(20),
birth date,
salary number(8,2)
);
//向表中插入数据
insert into test1 values(1001,'张三','20-5月',2300);
insert into test1 values(1002,'张三','01-6月',3000);
//查询表中的数据
select * from test1;
//新建第二张表,在表中不插入任何数据
create table test2(
eid number(10),
name varchar2(20),
birth date,
salary number(8,2)
);
//查询第二张表中的数据
select * from test2;
//使用merge进行数据合并
merge into test2
using test1
on(test.eid = test2.eid)
when matched then
update set name = test1.name, birth = test1.birth, salary = test1.salary
when not matched then
insert(eid,name,birth,salary) values(test1.eid, test1.name, test1.birth, test1.salaru);
select * from test2
merge into table_name talbe_alias //目标表
using(table | view | sub_query) alias //源表
on(join condition) //合并数据的条件
when matched then //如果条件成立则更新
update set col1=col_val, col2=co2_val...
when not matched then //如果条件不成立则插入
insert (column_list) values(column_values);
示例:
//新建一张表
create table test1(
eid number(10),
name varchar2(20),
birth date,
salary number(8,2)
);
//向表中插入数据
insert into test1 values(1001,'张三','20-5月',2300);
insert into test1 values(1002,'张三','01-6月',3000);
//查询表中的数据
select * from test1;
//新建第二张表,在表中不插入任何数据
create table test2(
eid number(10),
name varchar2(20),
birth date,
salary number(8,2)
);
//查询第二张表中的数据
select * from test2;
//使用merge进行数据合并
merge into test2
using test1
on(test.eid = test2.eid)
when matched then
update set name = test1.name, birth = test1.birth, salary = test1.salary
when not matched then
insert(eid,name,birth,salary) values(test1.eid, test1.name, test1.birth, test1.salaru);
select * from test2