Oracle融合语句

MERGE INTO table_name table_alias

USING (table|view|sub_query) alias

ON (join condition)

WHEN MATCHED THEN

UPDATE SET

col1 = col_val1,

col2 = col2_val

WHEN NOT MATCHED THEN

INSERT (column_list)

VALUES (column_values);

建立实验表，e1和e2表中有重复的人，但工资不同

CONN SCOTT/TIGER

DROP TABLE E1;

DROP TABLE E2;

CREATE TABLE E1 AS SELECT EMPNO,ENAME,SAL

FROM EMP WHERE DEPTNO=10;

CREATE TABLE E2 AS SELECT EMPNO,ENAME,SAL

FROM EMP WHERE DEPTNO IN (10,20);

UPDATE E2 SET SAL=SAL+100;

COMMIT;

SQL> select * from e1;

EMPNO ENAME SAL

---------- ---------- ----------

7782 CLARK 2450

7839 KING 5000

7934 MILLER 1300

SQL> select * from e2;

EMPNO ENAME SAL

---------- ---------- ----------

7369 SMITH 900

7566 JONES 3075

7782 CLARK 2550

7839 KING 5100

7902 FORD 3100

7934 MILLER 1400

这两张表有同名称的人，但工资不相同。

E1<----E2 将e2融合到e1表中

merge into E1

USING E2

ON (E1.EMPNO=E2.EMPNO)

WHEN MATCHED THEN

UPDATE SET

E1.SAL=E1.SAL

WHEN NOT MATCHED THEN

INSERT VALUES(E2.EMPNO,E2.ENAME,E2.SAL);

COMMIT;

SQL> SELECT * FROM E1 order by 1;

EMPNO ENAME SAL

---------- ---------- ----------

7369 SMITH 900

7566 JONES 3075

7782 CLARK 2450

7839 KING 5000

7902 FORD 3100

7934 MILLER 1300

6 rows selected.

SQL> SELECT * FROM E2 order by 1;

EMPNO ENAME SAL

---------- ---------- ----------

7369 SMITH 900

7566 JONES 3075

7782 CLARK 2550

7839 KING 5100

7902 FORD 3100

7934 MILLER 1400

E1表的前两行是自己的，没有变化，后面的行是e2表追加的。

Merge是update和insert的结合体，有做upate ,没有做insert。