Oracle中merge Into的用法
创始人
2024-05-29 06:23:24
0

Oracle中merge Into的用法

使用场景

在操作数据库时,数据存在的情况下,进行update操作;不存在的情况下,进行insert操作;在Oracle数据库中,能够使用merge into来实现。

基本语法

merge into table_name  alias1                  -- 目标表 可以用别名表示
using (table|view|sub_query) alias2            -- 数据源表 可以是表、视图、子查询等
on (join condition)                            -- 关联条件 
when matched then                              -- 当关联条件成立时 更新,删除,插入的where部分为可选 update table_name set col1 = colvalue  where ……        -- 更新操作delete from table_name  where  col2=colvalue  where……  -- 删除操作 -- 可以只更新不删除 也可以只删除不更新-- 如果更新和删除同时存在,删除的条件一定要在更新的条件内,否则数据不能删除
when not matched then                                    -- 当关联条件不成立时   
insert (col3) values (col3values)  where……               -- 关联条件进行插入操作

演示示例

为了演示,下面提供了两张测试表以及数据:

-- 测试表(1) tmp 
create table tmp
(id               VARCHAR2(20) not null,tmp_name        VARCHAR2(120),tmp_date  VARCHAR2(8),is_delete        VARCHAR2(1),creator          VARCHAR2(24),created_at       NUMBER(20),updater          VARCHAR2(24),updated_at       NUMBER(20)
)
-- 测试表(2) temp
create table temp
(id               VARCHAR2(20) not null,tmp_id        VARCHAR2(20),temp_name  VARCHAR2(120),temp_date  VARCHAR2(8),is_delete        VARCHAR2(1),creator          VARCHAR2(24),created_at       NUMBER(20),updater          VARCHAR2(24),updated_at       NUMBER(20)
)
-- 测试数据
truncate table tmp;
insert into tmp (ID, TMP_NAME, TMP_DATE, IS_DELETE, CREATOR, CREATED_AT, UPDATER, UPDATED_AT)values ('00001', 'tmp测试数据1', '20220628', '0', 'admin', null, null, null);
insert into tmp (ID, TMP_NAME, TMP_DATE, IS_DELETE, CREATOR, CREATED_AT, UPDATER, UPDATED_AT)values ('00002', 'tmp测试数据2', '20221223', '0', 'admin', null, null, null);
insert into tmp (ID, TMP_NAME, TMP_DATE, IS_DELETE, CREATOR, CREATED_AT, UPDATER, UPDATED_AT)values ('00003', 'tmp测试数据3', '20210927', '0', 'admin', null, null, null);
truncate table temp;
insert into temp (ID, TMP_ID, TEMP_NAME, TEMP_DATE, IS_DELETE, CREATOR, CREATED_AT, UPDATER, UPDATED_AT)values ('E74C9EC', '00001', 'temp测试数据1', '20210823', '0', 'admin', null, null, null);
insert into temp (ID, TMP_ID, TEMP_NAME, TEMP_DATE, IS_DELETE, CREATOR, CREATED_AT, UPDATER, UPDATED_AT)values ('39978FC', '00002', 'temp测试数据2', '20211012', '0', 'admin', null, null, null);
insert into temp (ID, TMP_ID, TEMP_NAME, TEMP_DATE, IS_DELETE, CREATOR, CREATED_AT, UPDATER, UPDATED_AT)values ('88640EF', '00006', 'temp测试数据3', '20211121', '0', 'admin', null, null, null);
commit;

tmp表数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pmolJugI-1677982321930)(Oracle%E4%B8%ADmerge%20Into%E7%9A%84%E7%94%A8%E6%B3%95.image/image-20230304152805601-16779148878521.png)]

temp表数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lryP65OA-1677982321931)(Oracle%E4%B8%ADmerge%20Into%E7%9A%84%E7%94%A8%E6%B3%95.image/image-20230304152832175.png)]

两表的关联关系

select t.*,tt.* from tmp t,temp tt where t.id = tt.tmp_id;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kqkekNP8-1677982321932)(Oracle%E4%B8%ADmerge%20Into%E7%9A%84%E7%94%A8%E6%B3%95.image/image-20230304153002916-16779150044203.png)]

merge into示例:

merge into temp  t   
using  tmp tt on (t.tmp_id = tt.id)
when matched then           update set t.temp_name ='xxkfz' where t.tmp_id = '00001'delete where (t.tmp_id  =  '00002')
when not matched then    insert (t.id, t.tmp_id,t.temp_name,t.temp_date,t.is_delete) values (sys_guid(),tt.id,tt.tmp_name,tt.tmp_date,tt.is_delete);commit;

几点说明:

  • 被更新的表写在merge into之后
  • 更新来源数据表写在using之后,并将相关字段查询出来,为查询结果定义别名
  • on 之后表示更新满足的条件
  • when matched then:表示当满足条件时要执行的操作
  • update set: 被更新表.被更新字段 = 更新表.更新字段—此更新语句不同于常规更新语句
  • when not matched then:表示当不满足条件时要执行的操作。
  • insert (被更新表.被更新字段,…) values (更新表.更新字段,…)
  • commit:表示提交事务

执行完成以上语句后,结果如下:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-31VFYQxq-1677982321932)(Oracle%E4%B8%ADmerge%20Into%E7%9A%84%E7%94%A8%E6%B3%95.image/image-20230304153734908-16779154570755.png)]

发现:temp表中tmp_id为00002的数据没有被删除。

why???

因为:如果更新和删除同时存在,删除的条件一定要在更新的条件内,否则数据不能删除!!!

为了更好的测试该场景,修改如下:

merge into temp  t    
using  tmp tt on (t.tmp_id = tt.id)
when matched then              update set t.temp_name ='xxkfz' where t.tmp_id  in ('00001','00002')delete where (t.tmp_id  =  '00002')
when not matched then    insert (t.id, t.tmp_id,t.temp_name,t.temp_date,t.is_delete) values (sys_guid(),tt.id,tt.tmp_name,tt.tmp_date,tt.is_delete);commit;

再次重置两表测试数据,执行以上语句,结果如下:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2AI5Dvhx-1677982321933)(Oracle%E4%B8%ADmerge%20Into%E7%9A%84%E7%94%A8%E6%B3%95.image/image-20230304154721686-16779160431659.png)]

发现tmp_id为00001的数据temp_name字段成功更新为xxkfz;tmp_id为00002的数据成功删除!!!

对于没有匹配的数据:tmp表id为00003的数据也成功插入带temp表中!!!

5Dvhx-1677982321933)]

发现tmp_id为00001的数据temp_name字段成功更新为xxkfz;tmp_id为00002的数据成功删除!!!

对于没有匹配的数据:tmp表id为00003的数据也成功插入带temp表中!!!

相关内容

热门资讯

中证A500ETF摩根(560... 8月22日,截止午间收盘,中证A500ETF摩根(560530)涨1.19%,报1.106元,成交额...
A500ETF易方达(1593... 8月22日,截止午间收盘,A500ETF易方达(159361)涨1.28%,报1.104元,成交额1...
何小鹏斥资约2.5亿港元增持小... 每经记者|孙磊    每经编辑|裴健如 8月21日晚间,小鹏汽车发布公告称,公司联...
中证500ETF基金(1593... 8月22日,截止午间收盘,中证500ETF基金(159337)涨0.94%,报1.509元,成交额2...
中证A500ETF华安(159... 8月22日,截止午间收盘,中证A500ETF华安(159359)涨1.15%,报1.139元,成交额...
科创AIETF(588790)... 8月22日,截止午间收盘,科创AIETF(588790)涨4.83%,报0.760元,成交额6.98...
创业板50ETF嘉实(1593... 8月22日,截止午间收盘,创业板50ETF嘉实(159373)涨2.61%,报1.296元,成交额1...
港股异动丨航空股大幅走低 中国... 港股航空股大幅下跌,其中,中国国航跌近7%表现最弱,中国东方航空跌近5%,中国南方航空跌超3%,美兰...
电网设备ETF(159326)... 8月22日,截止午间收盘,电网设备ETF(159326)跌0.25%,报1.198元,成交额409....
红利ETF国企(530880)... 8月22日,截止午间收盘,红利ETF国企(530880)跌0.67%,报1.034元,成交额29.0...