table_a表与table_b有关联关系

  --将 table_b表的数据【插入|更新】到table_a表中
  ## table_a表与table_b有关联关系
  merge into table_a a 
    using (select b.aid,b.name,b.year from table_b b) c on (a.id=c.aid)
  when matched then /*id已存在更新字段*/
       update set a.year=c.year,a.name=c.name
  when not matched then /*id不存在插入数据*/
       insert(a.id,a.name,a.year) values(c.aid,c.name,c.year); 

不在库中的数据判断是否在table_a表中，存在更新否则插入

merge into table_a a 
  using (select 'uuid1' as aid from dual) c on (a.id=c.aid)
when matched then/*id已存在更新字段*/
     update set a.year= 19, a.name='tom'
when not matched then /*id不存在插入数据*/
     insert(a.id,a.name,a.year) values('uuid1','tom',19); 

————————————— or—————————————

merge into table_a a 
  using (select 'uuid1' as aid ,'tom' as name, 19 as year from dual) c on (a.id=c.aid)
when matched then/*id已存在更新字段*/
     update set a.year= c.year, a.name=c.name
when not matched then /*id不存在插入数据*/
     insert(a.id,a.name,a.year) values(c.aid,c.name,c.year); 

使用虚表merge into 虚表需要内置

--*** 正确写法
 merge into table_a a 
  using (
  --虚表 temp_table
  with temp_table as (
       select ta.id as aid,ta.name as name,tb.year as year
       from table_a ta
       left join table_b tb on ta.idcard = tb.idcard  
  )
  --☆☆☆
 select * from temp_table
  ) c on (a.id=c.aid)
when matched then .........
when not matched then .........

--*** 错误写法写法
with temp_table as (
     select ta.id as aid,ta.name as name,tb.year as year
     from table_a ta
     left join table_b tb on ta.idcard = tb.idcard  
) 
merge into table_a a 
  using (select * from temp_table) c on (a.id=c.aid)
when matched then .........
when not matched then .........

merge into匹配条件可单独存在，如下

--只更新
merge into table_a a 
  using (select 'uuid1' as aid from dual) c on (a.id=c.aid)
when matched then 
    update set a.year= 19,a.name='tom'
--只插入
merge into table_a a 
  using (select 'uuid1' as aid from dual) c on (a.id=c.aid)
when not matched then 
   insert(a.id,a.name,a.year) values('uuid1','tom',19);