Oracle_Update...Set...From...解决方法Merge Into...Using...

 一,将要更新数据查到视图中,通过更新视图更新视图基表
 更新条件 源表尽量使单表，否则限制会比较多。
 1.distinct 关键字
 2.集合运算或分组函数。eg:intersect、sum、max、count ....
 3.出现group by 、order by、model、start with .....
 4.出现伪列关键字：eg:rownum.
 3.还应考虑基表的一些约束
 
 首先, A,update只能是单行对单行,单行对确定值的更新.
 B,多行对多行的更新本质上也是多个单行对单行的更新.
 A : col1p,col2p是tab1和tab2的主键
 UPDATE tab1
    SET tab1.col1 =
        (SELECT col2 FROM tab2 WHERE tab1.col1p = tab2.col2p);
 等价于
 UPDATE tab1 SET tab1.col1 = tab2.col2 FROM tab1 LEFT JOIN tab2 ON tab1.col1p = tab2.col2p;
 更新为确定值
 update tab1 set tab1.col1 = 'Accuracy' ;
 B,多行对多行
 先将要更新的多个表的数据查到一个视图中,然后通过更新视图的列值,更新对应的基表.
 UPDATE (SELECT tab1.col1, tab1.col11, tab2.col2, tab2.col22
           FROM tab1
           LEFT JOIN tab2
             ON tab1.col1p = tab2.col2p)
    SET col1  = col2,
        col11 = col22;
 等价于
 UPDATE tab1
    SET tab1.col1  = tab2.col2,
        tab1.col11 = tab2.col22 FROM tab1 LEFT JOIN tab2 ON tab1.col1p = tab2.col2p;
如果更新的内容不能一一对应会报错:ORA-01779: CANNOT MODIFY A COLUMN WHICH MAPS TO A NON KEY-PRESERVED TABLE
 解决:
 1,创建唯一索引:CREATE UNIQUE INDEX tab2_idx_uq ON tab2(col2p);
 二, MERGE INTO TABLE ... USING..
--定义
MERGE [ INTO ] [ schema. ] TABLE [ alias ]
USING { [ schema. ] TABLE | views | query} [ alias ]
ON {condition}
WHEN MATCHED THEN
    UPDATE SET {clause}
WHEN NOT MATCHED THEN
    INSERT VALUES {clause};

--Ecptdm为主键,when (not) matched可以分别操作筛选和未筛选出的数据
MERGE INTO ecm010test1
USING ecm010test2
ON (ecm010test1.Ecptdm = ecm010test2.ecptdm)
WHEN MATCHED THEN
    UPDATE SET ecm010test1.ecptmc = ecm010test2.ecptmc;
/*WHEN NOT MATCHED THEN
    UPDATE SET ecm010test1.ecptmc = ecm010test1.ecptmc;*/

--TEST Merge Into..
create table test1 as select 1 numid, 'vasd' varid from dual where 1=2;
create table test2 as select 1 numid, 'vasd' varid from dual where 1=2;
Select a.*,rowid From test1 a;
1    12    22      AAAXMHAAFAAC0zeAAA
2    11    11      AAAXMHAAFAAC0zeAAB
3    0    1       AAAXMHAAFAAC0zeAAC
Select a.*,rowid From test2 a;
1    22    22      AAAXMIAAFAAC0zWAAA
2    21    12      AAAXMIAAFAAC0zWAAB
3    0    2       AAAXMIAAFAAC0zWAAC

MERGE INTO test1
USING test2
ON (test1.numid = test2.numid)
WHEN MATCHED THEN --不需要填写更新的目标表
    UPDATE SET test1.varid = test2.varid
WHEN NOT MATCHED THEN --不需要填写插入的目标表
    INSERT VALUES (test2.numid, test2.varid);

Select a.* From test1 a;
1    12    22  
2    11    11  
3    0    2   
4    22    22  
5    21    12