最近由于项目需要一直想找个关于Oracle中实现根据一张表的内容更新另外张表数据的功能,在网上找了半天也没找到个合适的方法,虽然看到有人说可以通过存储过程来实现,但觉得那样太麻烦,今在查找同样的功能时发现了Oracle中的merge,于是花了点时间用比较简单的方法实现了这个功能。语法就免了,网上一搜一堆,看具体语句吧,代码也比较简单,通过字面就可以理解了,最简单的版本应是:
- merge into test1 t1
- using test2 t2
- on (t1.id = t2.id)
- when matched then
- update set t1.name = t2.name
- when not matched then
- insert values(t2.id,t2.name)
- merge into public_field t1
- using (select xx.link_id, yy.save_path
- from (select *
- from public_field x
- where x.link_id in (select t.id from scenic t)
- and x.ci_logo is null) xx
- left join (select *
- from IMAGE_INFO bb
- where bb.id in
- (select min(id) id
- from IMAGE_INFO a
- where a.link_id in (select t.id from scenic t)
- and a.link_tab = 'SCENIC'
- group by link_id)) yy on xx.link_id = yy.link_id) t2
- on (t1.link_id = t2.link_id)
- when matched then
- update
- set t1.ci_logo = t2.save_path
- --when not matched then
- --insert values(t2.id,t2.name)