描述: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);
本文出自:亿恩科技【www.enkj.com】