merge into 有则更新无则插入

 

其基本语法规则是

merge into 目标表 a

using 源表 b

on(a.条件字段1=b.条件字段1 and a.条件字段2=b.条件字段2 ……)  

when matched then update set a.更新字段=b.字段

when  not macthed then insert into a(字段1,字段2……)values(值1,值2……)

MERGE INTO table T1

           USING (SELECT '90040124' AS a FROM dual) T2
          ON ( T1.COM_CODE=T2.a)
  WHEN MATCHED THEN
          UPDATE SET T1.UPDATETIME = sysdate
  WHEN NOT MATCHED THEN 
          INSERT (T1.COM_CODE,T1.COM_NAME) VALUES('8000208','321');