merge into语句

merge into 语句使用的场景：存在匹配条件的记录则更新，不存在则插入新的记录。

merge into语句的语法

MERGE INTO 表名 别名1 
USING (table|view|sub_query) 别名2
ON (JOIN condition)
WHEN MATCHED THEN 
    --如果存在，更新
    UPDATE SET 
    别名1.col1 = 别名2.col, 
    别名1.col2 = 别名2.col
WHEN NOT MATCHED THEN
    --如果不存在，新增
    INSERT (column1, column2) VALUES (别名2.column1, 别名2.column2);

判断Ｂ表和Ａ表是否满足ON中条件，如果满足则执行满足后面的update SQL，如果不满足，则执行后面的insert SQL。

merge 语句的各种用法练习

创建表及插入目录

create table t_B_info_aa
( id varchar2(20),
  name varchar2(50),
  type varchar2(30),
  price number
);
insert into t_B_info_aa values('01','冰箱','家电',2000);
insert into t_B_info_aa values('02','洗衣机','家电',1500);
insert into t_B_info_aa values('03','热水器','家电',1000);
insert into t_B_info_aa values('04','净水机','家电',1450);
insert into t_B_info_aa values('05','燃气灶','家电',800);
insert into t_B_info_aa values('06','太阳能','家电',1200);
insert into t_B_info_aa values('07','西红柿','食品',1.5);
insert into t_B_info_aa values('08','黄瓜','食品',3);
insert into t_B_info_aa values('09','菠菜','食品',4);
insert into t_B_info_aa values('10','香菇','食品',9);
insert into t_B_info_aa values('11','韭菜','食品',2);
insert into t_B_info_aa values('12','白菜','食品',1.2);
insert into t_B_info_aa values('13','芹菜','食品',2.1);

create table t_B_info_bb
( id varchar2(20),
  type varchar2(50),
  price number
);
insert into t_B_info_bb values('01','家电',2000);
insert into t_B_info_bb values('02','家电',1000);

update和insert同时使用

merge into t_B_info_bb b
using t_B_info_aa a -- 如果是子查询要用括号括起来
on (a.id = b.id and a.type = b.type) -- 关联条件要用括号括起来
when matched then
  update set b.price = a.price -- update 后面直接跟set语句
when not matched then
  insert (id, type, price) values (a.id, a.type, a.price) -- insert 后面不加into
  
---- 这条语句根据t_B_info_aa 更新了t_B_info_bb中的一条记录，插入了11条记录

只插入不更新

-- 处理表中数据，使表中的一条数据发生变化，删掉一部分数据。用来验证只插入不更新的功能
update t_B_info_bb b set b.price=1000 where b.id='02';
delete from t_B_info_bb b where b.type='食品';
  
-- 只是去掉了when matched then update语句
merge into t_B_info_bb b
using t_B_info_aa a
on (a.id = b.id and a.type = b.type)
when not matched then
  insert (id, type, price) values (a.id, a.type, a.price)

只更新不插入 

-- 处理表中数据，删掉一部分数据，用来验证只更新不插入
delete from t_B_info_bb b where b.type='食品';

-- 只更新不插入，只是去掉了when not matched then insert语句
merge into t_B_info_bb b
using t_B_info_aa a
on (a.id = b.id and a.type = b.type)
when matched then
   update set b.price = a.price

加入限制条件的操作 

变更表中数据，便于练习：

update t_B_info_bb b set b.price = 1000 where b.id='02';
delete from t_b_info_bb b where b.id not in ('01','02') and b.type='家电';
update t_B_info_bb b set b.price = 8 where b.id='10';
delete from t_b_info_bb b where b.id  in ('11','12','13') and b.type='食品';

t_B_info_bb表数据：

执行merge语句：脚本一和脚本二执行结果相同

脚本一：

merge into t_B_info_bb b
     using t_B_info_aa a
     on (a.id = b.id and a.type = b.type)
     when matched then
       update set b.price = a.price where a.type = '家电'
     when not matched then
       insert
         (id, type, price)
       values
         (a.id, a.type, a.price) where a.type = '家电';

脚本二：

merge into t_B_info_bb b
     using (select a.id, a.type, a.price
              from t_B_info_aa a
             where a.type = '家电') a
     on (a.id = b.id and a.type = b.type)
     when matched then
       update set b.price = a.price
     when not matched then
       insert (id, type, price) values (a.id, a.type, a.price);

结果：

 

上面两个语句是只对类型是家电的语句进行插入和更新。

脚本三：只对家电进行更新，其余的全部进行插入

merge into t_B_info_bb b
     using t_B_info_aa a
     on (a.id = b.id and a.type = b.type)
     when matched then
       update set b.price = a.price
        where a.type = '家电'
     when not matched then
       insert
         (id, type, price)
       values
         (a.id, a.type, a.price);

结果：

加删除操作

update子句后面可以跟delete子句来去掉一些不需要的行。

delete只能和update配合，从而达到删除满足where条件的子句的记录。

merge into t_B_info_bb b
     using t_B_info_aa a
     on (a.id=b.id and a.type = b.type)
     when matched then 
       update set b.price=a.price
       delete where (a.type='食品')
     when not matched then
       insert values (a.id, a.type, a.price) where a.id = '10';

mybatis中批量merge into

<update id="mergeinto">
    merge into user_type a
    using (
    <foreach collection="list" item="item" separator="union all">
        select
        #{item.name,jdbcType=VARCHAR} as name,
        #{item.type,jdbcType=VARCHAR} as type
        from dual
    </foreach>
    ) b
    on (a.type = b.type)
    when matched then
        update set name = #{name}
    when not matched then
        insert  (type,name) values(#{type},#{name})
</update>

注意从list中的对象取字段的值是用#{item.name}方式，而不是直接用#{name}。

Mapper.java文件中代码是这样写的：

@Mapper
public interface UserTypeMapper {
    void mergeinto(@Param("list") List<UserType> list);
}