达梦数据批量跟新和插入

如题

使用 merge into

表结构

如下
CREATE TABLE "TEST"."GOODS_1" ( "GID" BIGINT, "GNAME" VARCHAR(50), "USER_ID" BIGINT, "GSTATUS" VARCHAR(50)) STORAGE(ON "MAIN", CLUSTERBTR) ;

MERGE INTO TEST.GOODS_1 a 
USING (
    SELECT 11 as gid, 't444' as gname, 1 as user_id, 11 as gstatus FROM dual
    UNION ALL
    SELECT 22 as gid, 't244' as gname, 1 as user_id, 11 as gstatus FROM dual
     UNION ALL
    SELECT 33 as gid, 't244' as gname, 1 as user_id, 11 as gstatus FROM dual
) b
ON (a.gid = b.gid)  -- 需要指定匹配条件
WHEN NOT MATCHED THEN
    INSERT (gid, gname, user_id, gstatus)
    VALUES (b.gid, b.gname, b.user_id, b.gstatus)
when matched then
   UPDATE SET 
    
            a.gname = b.gname,
            a.user_id = b.user_id,
            a.gstatus = b.gstatus

对应mybatis ximl写法如下

<update id="mergeGoods">
    MERGE INTO TEST.GOODS_1 a 
    USING (
        <foreach collection="list" item="item" separator="UNION ALL">
            SELECT 
                #{item.gid} as gid,
                #{item.name} as name,
                #{item.quantity} as quantity,
                #{item.price} as price
            FROM dual
        </foreach>
    ) b
    ON (a.gid = b.gid)
    WHEN MATCHED THEN
        UPDATE SET 
            a.name = b.name,
            a.quantity = b.quantity,
            a.price = b.price,
            a.update_time = SYSDATE
    WHEN NOT MATCHED THEN
        INSERT (gid, name, quantity, price, create_time)
        VALUES (b.gid, b.name, b.quantity, b.price, SYSDATE)
</update>