oracle的merge

merge into TBWKRECMAINTAINFUNDINFO t
   using (select 3 as fid, 456 as OPTYPENUM, 2 as recyear, 2 recnum from dual) t1
   on(t.fid = t1.fid)
    when not matched then
    insert(FID,OPTYPENUM,recyear,recnum) values(3, 3, 3, 3);

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
ORACLEMERGE语句是一种强大的SQL语法,它可以在一个语句中同时执行插入和更新操作。MERGE语句的语法结构包括以下几个部分: 1. MERGE INTO [schema.]table [t_alias]: 指定要执行插入和更新操作的目标表和可选的别名。 2. USING [schema.] { table | view | subquery } [t_alias]: 指定用于匹配和源数据的来源表、视图或子查询,并可选地为其指定别名。 3. ON (condition): 指定用于匹配目标表和源表数据的条件。 4. WHEN MATCHED THEN merge_update_clause: 指定当目标表和源表数据匹配时要执行的更新操作。 5. WHEN NOT MATCHED THEN merge_insert_clause: 指定当目标表和源表数据不匹配时要执行的插入操作。 下面是几个关于ORACLE MERGE语句的例子: (A) ```sql merge into plch_members m using plch_members_stage s on (m.id = s.id) when matched then when s.dmltype = 'U' then update set m.name = s.name when s.dmltype = 'D' then delete m when not matched then insert (m.id, m.name) values (s.id, s.name) ``` (B) ```sql merge into plch_members m using plch_members_stage s on (m.id = s.id) when matched then update set m.name = s.name delete where s.dmltype = 'D' when not matched then insert (m.id, m.name) values (s.id, s.name) ``` (C) ```sql merge into plch_members m using plch_members_stage s on (m.id = s.id) when matched then update set m.name = s.name where s.dmltype = 'U' delete where s.dmltype = 'I' when not matched then insert (m.id, m.name) values (s.id, s.name) ``` (D) ```sql merge into plch_members m using plch_members_stage s on (m.id = s.id) when matched then update set m.name = nvl(s.name, 'DUMMY') delete where s.dmltype = 'D' when not matched then insert (m.id, m.name) values (s.id, s.name) ``` (E) ```sql merge into plch_members m using plch_members_stage s on (m.id = s.id) when matched then update set m.name = case s.dmltype when 'U' then s.name when 'D' then 'DUMMY' end delete where s.dmltype = 'D' when not matched then insert (m.id, m.name) values (s.id, s.name) ``` 这些例子展示了不同的MERGE语句用法,包括如何根据条件更新、删除和插入数据。在实际应用中,你可以根据具体的需求和数据情况选择适合的MERGE语句来实现数据操作。<span class="em">1</span><span class="em">2</span><span class="em">3</span><span class="em">4</span>
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值