oracle 更新相同表问题

描述：ta,tb两表的结构完全相同，现在想要以tb中的数据去更新ta表， 要求：以ta为准，若ta中没有的数据，将tb中的数据完全合并到ta中； 若ta中有的数据，但不完全，一些字段为空，那么将tb中相同id的字段去更新ta表， -- 方法一:用全连接，结合nvl函数： with ta as( select 1 id, 23 age, 'lilei' name, 'ddd@126.com' mail from dual union all select 2, null, 'hanmeimei',null from dual union all select 3, 23, null, 'jim eee@153.com' from dual union all select 4, 22, 'tom',null from dual), tb as( select 1 id, 23 age, 'lilei' name, 'bbb@126.com' mail from dual union all select 2, 25, 'hanmeimei', 'fff@124com' from dual union all select 5, 27, 'green', 'ejorj@125.com' from dual) select nvl(ta.id,tb.id) id, nvl(ta.age,tb.age) age, nvl(ta.name,tb.name) name, nvl(ta.mail,tb.mail) mail from ta full join tb on ta.id=tb.id order by id; ID AGE NAME MAIL ---------- ---------- --------- --------------- 1 23 lilei ddd@126.com 2 25 hanmeimei fff@124com 3 23 jim eee@153.com 4 22 tom 5 27 green ejorj@125.com -- 方法二:使用merge into合并： create table ta(id varchar2(2),age number(3),name varchar2(10),mail varchar2(30)); select * from ta; ID AGE NAME MAIL -- ---- ---------- ------------------------------ 1 23 lilei ddd@126.com 2 hanmeimei 3 23 jim eee@153.com 4 22 tom -- create table tb as select * from ta where 1=0; select * from tb; ID AGE NAME MAIL -- ---- ---------- ------------------------------ 1 23 lilei bbb@126.com 2 25 hanmeimei fff@124.com 5 27 green ejorj@125.com -- merge into ta using tb on (ta.id=tb.id) when matched then update set age=COALESCE(ta.age,tb.age), name=COALESCE(ta.name,tb.name), mail=COALESCE(ta.mail,tb.mail) when not matched then insert(ta.id,ta.age,ta.name,ta.mail) values(tb.id,tb.age,tb.name,tb.mail); -- ID AGE NAME MAIL -- ---- ---------- ------------------------------ 1 23 lilei ddd@126.com 2 25 hanmeimei fff@124.com 3 23 jim eee@153.com 4 22 tom 5 27 green ejorj@125.com -- 方法三:使用update直接更新ta表,若ta中没有的数据,将tb中的数据添加进来即可： 3.1 更新 update ta a set (a.age,a.name,a.mail)=( select nvl(a1.age,b1.age), nvl(a1.name,b1.name), nvl(a1.mail,b1.mail) from ta a1,tb b1 where a1.id=b1.id and a1.id=a.id) where exists (select 1 from ta a2 where a2.id=a.id); //注意：此方法失败，如果您能想到解决办法，请赐教 3.2 添加 insert into ta(id,age,name,mail) select tb.id,tb.age,tb.name,tb.mail from tb where tb.id not in(select ta.id from ta); 

原帖：http://topic.youkuaiyun.com/u/20110419/02/5fd87b61-9994-4340-860b-d0bb5ad11adf.html