关于Orcle的MERGE INTO 如果存在重复数据 覆盖或忽略

最后更新:2025-07-02 23:32:15 | 状态:未完成
1
2
service.insert(table, set, new DefaultConfigStore().override(true, "ID"));
service.insert(table, set, new DefaultConfigStore().override(false, "ID"));
最终要生成的SQL:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
MERGE INTO CRM_USER M
USING (
        SELECT
            I.ID AS ID, I.CODE AS CODE, I.NAME AS NAME
        FROM(
            SELECT 12 AS ID,1 AS CODE,12 AS NAME   FROM DUAL
            UNION ALL
            SELECT 22 AS ID,1 AS CODE,22 AS NAME  FROM DUAL  
        ) I
) D ON (D.ID=M.ID)
WHEN NOT MATCHED THEN
    INSERT(M.ID,M.CODE,M.NAME) VALUES(D.ID,D.CODE,D.NAME)
WHEN MATCHED THEN
    UPDATE SET M.CODE=D.CODE, M.NAME = D.NAME
 USING中是一个子查询,当然也可以从另一个表中SELECT
 其中ON(M.ID=D.ID)类似于JOIN的关联条件,匹配成功表示数据已存在,执行<span style="text-wrap:wrap;">WHEN MATCHED THEN分支 ,否则支持<span style="text-wrap:wrap;">WHEN NOT MATCHED THEN</span></span>分支
 WHEN NOT MATCHED THEN WHEN MATCHED THEN 可以只用一个
首页 最近更新 搜索 提交 回复