Oracle 更新相同表问题
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);
评论暂时关闭