Oracle database 使用SQL转换数据 使用 merge语句 理论试验

1、使用Merge语句

为了方便，先把原来生成的表列名字改回去

alter table new_employees4_yearly_payment
rename column yearly_salary to salary_new;

先看看目标表：

SQL> select * from new_employees4;

EMPLOYEE_ID SALARY_NEW DEPARTMENT_ID LAST_NAME
----------- ---------- ------------- -------------------------
FIRST_NAME
--------------------
	108    13208.8		 100 Greenberg
Nancy

	109	  9900		 100 Faviet
Daniel

	110	  9020		 100 Chen
John

再看看源表：

select * from new_employees4_yearly_payment;

EMPLOYEE_ID SALARY_NEW DEPARTMENT_ID LAST_NAME
----------- ---------- ------------- -------------------------
FIRST_NAME
--------------------
	120	 96000		  66 kong
weiyi

	120	  1200		  66 liu
yifei

	120	  1200		  66 fan
binbin

然后使用merge语句把原表更新：

merge into new_employees4 aa

using new_employees4_yearly_payment  bb

on (aa.department_id=bb.department_id)

when matched then 

update set aa.salary_new=bb.salary_new*1.1

when not matched 

then insert (first_name, last_name, employee_id, salary_new, department_id)

VALUES (bb.first_name,bb.last_name,bb.employee_id,bb.salary_new,bb.department_id);

注意这里values里面的列顺序要和目标表的列顺序一致，否者如果出现列的属性对不上的情况会报错：ORA-01722: invalid number（或其它）  这是为什么呢？？？

然后再看看目标表：

SQL> select * from new_employees4;

EMPLOYEE_ID SALARY_NEW DEPARTMENT_ID LAST_NAME
----------- ---------- ------------- -------------------------
FIRST_NAME
--------------------
	108    13208.8		 100 Greenberg
Nancy

	109	  9900		 100 Faviet
Daniel

	110	  9020		 100 Chen
John


EMPLOYEE_ID SALARY_NEW DEPARTMENT_ID LAST_NAME
----------- ---------- ------------- -------------------------
FIRST_NAME
--------------------
	120	  1200		  66 fan
binbin

	120	  1200		  66 liu
yifei

	120	 96000		  66 kong
weiyi


6 rows selected.

SQL>