Merge 语句就是依据条件update或者insert数据到目的表中,语句结构如下:

MERGE [hint] INTO [schema .] table [t_alias] USING [schema .] 
{ table | view | subquery } [t_alias] ON ( condition ) 
WHEN MATCHED THEN merge_update_clause 
WHEN NOT MATCHED THEN merge_insert_clause;

具体看看下面这个例子......

sql>select empno,ename,sal,deptno
2 from emp;

EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7369 SMITH 800 20
7499 ALLEN 1600 30
7521 WARD 1250 30
7566 JONES 2975 20
7654 MARTIN 1250 30
7698 BLAKE 2850 30
7788 SCOTT 3000 20
7844 TURNER 1500 30
7876 ADAMS 1100 20
7900 JAMES 950 30
7902 FORD 3000 20

11 rows selected.

sql>create table test as
2 select empno,ename,sal,deptno from emp where deptno = '20';

Table created.

sql>select * from test;

EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7369 SMITH 800 20
7566 JONES 2975 20
7788 SCOTT 3000 20
7876 ADAMS 1100 20
7902 FORD 3000 20
sql>merge into test
2 using (select * from emp) e
3 on test.empno = e.empno
4 when matched then update set sal = sal + 200
5 when not matched then insert (test.empno,test.ename,test.sal,test.deptno)
6 values(e.empno,e.ename,e.sal,e.deptno);
on test.empno = e.empno
*
ERROR at line 3:
ORA-00969: missing ON keyword

sql>ed
Wrote file afiedt.buf

1 merge into test
2 using (select * from emp) e
3 on (test.empno = e.empno)
4 when matched then update set sal = sal + 200
5 when not matched then insert (test.empno,test.ename,test.sal,test.deptno)
6* values(e.empno,e.ename,e.sal,e.deptno)
sql>/

11 rows merged.

sql>select * from test;

EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7369 SMITH 1000 20
7566 JONES 3175 20
7788 SCOTT 3200 20
7876 ADAMS 1300 20
7902 FORD 3200 20
7844 TURNER 1500 30
7698 BLAKE 2850 30
7499 ALLEN 1600 30
7521 WARD 1250 30
7654 MARTIN 1250 30
7900 JAMES 950 30

11 rows selected.

ITPub博客

About Merge statement

最新文章