oracle的merge语句测试

Merge,顾名思义就是合并两个表的数据,其语法是:


下面是一个具体的例子:

--准备数据,从new_persons表合并到persons表
ADAM@orcl>create table persons(id number, name varchar2(20));
Table created.

ADAM@orcl>insert into persons values(1,'adam');

1 row created.

ADAM@orcl>insert into persons values(2,'jack');

1 row created.

ADAM@orcl>create table new_persons as select * from persons where 1= 0;

Table created.

ADAM@orcl>insert into new_persons values (1, 'adam.xing');

1 row created.

ADAM@orcl>insert into new_persons values (3, 'brouce');

1 row created.

ADAM@orcl>select * from persons;
	ID NAME
---------- --------------------
	 1 adam
	 2 jack

ADAM@orcl>select * from new_persons;
	ID NAME
---------- --------------------
	 1 adam.xing
	 3 brouce

--合并后,persons表id=1的记录中name变成adam.xing,另外增加id=3的记录
ADAM@orcl>merge into persons using new_persons on (persons.id = new_persons.id )
  2  when matched then update set persons.name = new_persons.name
  3  when not matched then insert values(new_persons.id, new_persons.name);

2 rows merged.

ADAM@orcl>select * from persons;
	ID NAME
---------- --------------------
	 1 adam.xing
	 2 jack
	 3 brouce

ADAM@orcl>insert into new_persons values (4, 'michael');

1 row created.

ADAM@orcl>update new_persons set name = 'adam.xing.new' where id = 1;

1 row updated.

ADAM@orcl>select * from new_persons;
	ID NAME
---------- --------------------
	 1 adam.xing.new
	 3 brouce
	 4 michael

--只新增记录的写法
ADAM@orcl>merge into persons using new_persons on (persons.id = new_persons.id )
  2  when not matched then insert values(new_persons.id, new_persons.name);

1 row merged.

ADAM@orcl>select * from persons;
	ID NAME
---------- --------------------
	 1 adam.xing
	 2 jack
	 3 brouce
	 4 michael

--只更新记录的写法
ADAM@orcl>merge into persons using new_persons on (persons.id = new_persons.id )
  2  when matched then update set persons.name = new_persons.name;

3 rows merged.

ADAM@orcl>select * from persons;
	ID NAME
---------- --------------------
	 1 adam.xing.new
	 2 jack
	 3 brouce
	 4 michael

--删除记录的写法,注意delete操作的基础是符合update条件的记录
ADAM@orcl>create table blacklists as select * from persons where 1 = 0;

Table created.

ADAM@orcl>insert into blacklists values(2, 'jack');

1 row created.

ADAM@orcl>merge into persons using blacklists on (persons.id = blacklists.id )
  2  when matched then update set name = blacklists.name
  3  delete where persons.id = blacklists.id;

1 row merged.

ADAM@orcl>select * from persons;
	ID NAME
---------- --------------------
	 1 adam.xing.new
	 3 brouce
	 4 michael

参考资料:
oracle-merge用法详解

Comments are closed.