创建表t4，t5
SQL> create table t4(id int ,name varchar2(10));

Table created.

SQL> create table t5(id int ,name varchar2(10));

Table created.

构造数据

SQL> insert into t4 values(1,'a');

1 row created.

SQL> insert into t4 values(2,'b');

1 row created.

SQL> insert into t4 values(3,'');

1 row created.

SQL> insert into t4 values(5,'');

1 row created.

SQL> insert into t5 values(5,'t5-c');

1 row created.

SQL> insert into t5 values(1,'t5-a');

1 row created.

SQL> insert into t5 values(2,'t5-b');

1 row created.

SQL> insert into t5 values(3,'t5-c');

1 row created.

SQL> insert into t5 values(4,'t5-d');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t4;

ID NAME
---------- ----------
1 a
2 b
3
5

SQL> select * from t5;

ID NAME
---------- ----------
5 t5-c
1 t5-a
2 t5-b
3 t5-c
4 t5-d

合并：

SQL> merge into t4
2 using (select * from t5) t5
3 on (t4.id=t5.id)
4 when matched then update set name=t5.name
5 when not matched then insert values(t5.id,t5.name)
6 /

5 rows merged.

结果查询：
SQL> select * from t4;

ID NAME
----- ----------
1 t5-a
2 t5-b
3 t5-c
5 t5-c
4 t5-d 这行是insert ，其他都是update

ITPub博客

merge 简单测试

最新文章