1.语法
merge into ...
using ...
on ...
when matched then
update set ...
when matched then
insert values...
2.实验
create table emp(id number,name varchar2(20),age number);
插入数据
insert into emp values(1,'guo',23);
insert into emp values(2,'joe',22);
insert into emp values(3,'liu',27);

SQL> select * from emp;
ID NAME AGE
---------- -------------------- ----------
1 guo 23
2 joe 22
3 liu 27
create table person as select * from emp where 1=0;
insert into person values(1,'db',35);
SQL> select * from person;

ID NAME AGE
---------- -------------------- ----------
1 db 35

merge into person p
using emp e
on (e.id=p.id)
when matched then
update set p.name=e.name,p.age=e.age
when not matched then
insert values(e.id,e.name,e.age)

SQL> select *from emp;

ID NAME AGE
---------- -------------------- ----------
1 guo 23
2 joe 22
3 liu 27

SQL> select *from person;

ID NAME AGE
---------- -------------------- ----------
1 guo 23
3 liu 27
2 joe 22

注意点：on后面要用括号
update语句中的表名称不需要
insert语句中into 表也不需要

用一张表的数据去更新另一张表，如果表中关联字段的数据相同则更新，否则插入

ITPub博客

merge使用

最新文章