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 ecm010test1USING ecm010test2ON (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      AAAXMHAAFAAC0zeAAA2    11    11      AAAXMHAAFAAC0zeAAB3    0    1       AAAXMHAAFAAC0zeAACSelect a.*,rowid From test2 a;1    22    22      AAAXMIAAFAAC0zWAAA2    21    12      AAAXMIAAFAAC0zWAAB3    0    2       AAAXMIAAFAAC0zWAACMERGE INTO test1USING test2ON (test1.numid = test2.numid)WHEN MATCHED THEN --不需要填写更新的目标表    UPDATE SET test1.varid = test2.varidWHEN 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