在Oracle中,單引號有兩個作用: |
發(fā)布時間: 2012/9/12 17:27:48 |
描述:ta,tb兩表的結構完全相同,現(xiàn)在想要以tb中的數(shù)據(jù)去更新ta表, 要求:以ta為準,若ta中沒有的數(shù)據(jù),將tb中的數(shù)據(jù)完全合并到ta中; 若ta中有的數(shù)據(jù),但不完全,一些字段為空,那么將tb中相同id的字段去更新ta表, -- 方法一:用全連接,結合nvl函數(shù): 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中沒有的數(shù)據(jù),將tb中的數(shù)據(jù)添加進來即可: 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); 本文出自:億恩科技【mszdt.com】 |