1.Insert data in db temp table from excel data
2.update data from temp table
上述的從Temp table取用資料Update資料表，就切入本篇的主題，可用的方法有三：
1.Update by sub-query
2.Update table view
3.Merge
方法一：
UPDATE bigTable b
SET (col1,col2,col3) = (
SELECT t.col1,t.col2,t.col3
FROM tempTable t
WHERE b.col = t.col)
where exists(
SELECT t.col1,t.col2,t.col3
FROM tempTable t
WHERE b.col = t.col);
方法二：
UPDATE (
SELECT b.col1 as old_col1,
b.col2 as old_col2,
b.col3 as old_col3,
t.col1 as new_col1,
t.col2 as new_col2,
t.col3 as new_col3
FROM bigTable b, tempTable t
WHERE b.col = t.col)
SET old_col1 = new_col1,
old_col2 = new_col2,
old_col3 = new_col3;
方法三：
MERGE INTO bigTable b
USING (SELECT col1 , col2 , col3 , col
tempTable t ) t
ON ( b.col = t.col)
WHEN matched THEN
UPDATE
SET old_col1 = new_col1,
old_col2 = new_col2,
old_col3 = new_col3;
測試結果：
方法一：140 sec
方法二：1 sec
方法三：未測透過方法二 明顯是效能最佳的解法，但若會被update的bigTable並非符合UK或PK的條件，將會產生"ORA-01779: cannot modify a column which maps to a non-key-preserved table"的錯誤，若你的table不適合建立UK或PK時，可透過hint的方式/*+ BYPASS_UJVC */來忽略UK的檢查。
UPDATE (
SELECT /*+ BYPASS_UJVC */ b.col1 as old_col1,
b.col2 as old_col2,
b.col3 as old_col3,
t.col1 as new_col1,
t.col2 as new_col2,
t.col3 as new_col3
FROM bigTable b, tempTable t
WHERE b.col = t.col)
SET old_col1 = new_col1,
old_col2 = new_col2,
old_col3 = new_col3;
參考網址：http://blog.csdn.net/yuhua3272004/archive/2008/08/06/2776121.aspx
