merge into用法

清疚 2024-03-23 15:16 92阅读 0赞

### merge into用法 ###

### 第一种形式-参数为数据List ###

<update id="mergeInto">
            merge into people a
            using
            (
            <foreach collection="list" index="index" item="item" open=""
                     close="" separator="union all">
                SELECT
                #{item.ID,jdbcType=NUMERIC} as ID,
                #{item.NAME,jdbcType=NUMERIC} as NAME,
                #{item.sex,jdbcType=STRING} as sex
                FROM dual
            </foreach>
            ) b
            on (
    		#查询条件，查询条件的的字段不能用于修改update操作
            a.ID= b.ID
            AND a.NAME= b.NAME
            )
            when
            #如果匹配 更新
            matched then
            UPDATE SET
            a.sex= b.sex
            when
            #如果不匹配 插入
            not matched then
            INSERT(
            a.ID,
            a.NAME,
            a.sex
            ) VALUES(
            b.ID,
            b.NAME,
            b.sex
            )
        </update>

### 第二种形式-参数为时间范围 ###

<insert id="batchMergeIntoByTimeFrame" parameterType="testEntity ">
            MERGE INTO target_table target
            USING (
            SELECT
            tp.*
            FROM
            source_table tp
            WHERE
            tp.ADD_DATE >= to_date(#{params.beginTime},'yyyy-MM-dd HH24:mi:ss')
            AND tp.ADD_DATE <= to_date(#{params.endTime},'yyyy-MM-dd HH24:mi:ss')
            ) source
            ON (target.ID = source.ID)
            #如果匹配 更新
            when matched THEN
            UPDATE SET
            target.REMARK= source.REMARK
            WHEN NOT MATCHED THEN
            INSERT(
            target.ID,
            target.REMARK,
            target.PROPERTY_01,
            target.ADD_BY,
            target.ADD_DATE
            )
            VALUES(
            source.ID,
            source.REMARK,
            source.PROPERTY_01,
            source.ADD_BY,
            source.ADD_DATE
            )
        </insert>