03-011 update与merge into

最新推荐文章于 2024-09-09 22:24:39 发布

原创最新推荐文章于 2024-09-09 22:24:39 发布 · 651 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#merge into

database 专栏收录该内容

24 篇文章

订阅专栏

本文探讨了在SQL中使用Update语句和MergeInto语句更新表数据的复杂场景，包括处理多对一关系、避免数据丢失及解决重复值问题。通过实例分析，对比了不同方法的优缺点。

文章目录

Update 语句

Update 语句用于修改表中的数据。

语法：

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

创建表
create table table1(  
idd varchar2(10) ,  
val varchar2(20)  
); 
create table table2(  
idd varchar2(10),  
val varchar2(20)  
);
插入数据
insert into table1 values ('01','1111');
insert into table1 values ('02','222');  
insert into table1 values ('02','2222');    
insert into table1 values ('03','3333');  
insert into table1 values ('04','4444');
insert into table1 values ('06','6666');
commit;  
insert into table2 values ('01','aaaa');
insert into table2 values ('02','bbbb');      
insert into table2 values ('03','cccc'); 
insert into table2 values ('04','dddd');
insert into table2 values ('05','eee');
commit;

在这里插入图片描述

要将 table2中idd - val 的值，赋值给table1对应的 idd - val；

为了验证操作的合理性，设置了如下几个需要额外考虑情况：
注意两表特殊地方在于：
1、table1中，有1条idd字段值为06的数据，table2中idd字段没有06，命名为 x1；
2、table1中，有2条idd字段值都为02，并且对应的val 不同的数据，命名为 x2，以下都能正常解决此情况；

3、table2中，有2条idd字段值都为05，但对应的val值不同的数据，命名为 x3，待插入数据验证；

①、直接根据`表2`更新`表1`

update table1 set table1.val = (select val from table2 where table1.idd = table2.idd);

如下图，idd=06的值为null了
在这里插入图片描述
问题：我们遇到了x1情况，即table1中06对应的值被改变了–>val变成了null（即图中的空白）；
这并不是我们的本意，故做出如下改进。

②、加入限制条件，对于 table1中有值，但是table2中无值的idd字段，不做修改；

update table1 set val = (select val from table2 where table1.idd = table2.idd)
where exists (select 1 from table2 where table1.idd = table2.idd)

在这里插入图片描述
第2种写法看似没问题，但如果我们再次向table2中插入一条数据，

insert into table2 values ('03','ccc'); 遇到了x3情况，

执行后会报错如下：
ORA-01427:单行子查询返回多个行
在这里插入图片描述

③、通过上述分析，简单的更新语句并不能解决遇到的异常情况。所以我们可以使用merge，如下

merge into table1
using  (select t.idd ,max(t.val) m from table2 t group by t.idd)table2
on (table1.idd = table2.idd)
when matched then
update set table1.val = table2.m

merge into使用

MERGE INTO [target-table] A USING [source-table sql] B ON([conditional expression] and [...]...)
WHEN MATCHED THEN
	[UPDATE sql]
WHEN NOT MATCHED THEN
	[INSERT sql]

merge into ams_asset_info t1
using ams_asset_info_diff_domain t2
on (t1.assetno=t2.assetno)
when matched then
  update set t1.domain=t2.domain;
  commit;

使用merge into是为了根据匹配条件on(condition)利用table_source 的数据更新合并table_target的数据。
merge into的内部处理是将table_source的每一条记录和table_target的每一条记录对比匹配，匹配到符合条件的记录就会进行修改，匹配不到的话就会insert。如果table_source的匹配列中有重复值的话，等到第二次重复的列值匹配的时候，就会将第一次的update后的值再一次update,就是说合并后的table_target中会丢失在table_source中的记录。如果记录丢失的话，两表合并就无意义了。因此我们使用merge into要注意：源表匹配列中不能有重复值，否则无法匹配报错(报错信息：ORA-30926: 无法在源表中获得一组稳定的行
)。

merge into dhmp_ams.ams_asset_info t1
using

 (select *
    from (select mac,
                 remark2,
                 row_number() over(partition by mac order by mac) rank
            from rms.itms_order_info
           where mac is not null
             and product_type in (1, 7)
             and remark2 is not null)
   where rank = 1) t2

on (t1.ottmac = t2.mac)
when matched then
  update set t1.contactinfo = t2.remark2