关于Orcle的MERGE INTO 如果存在重复数据 覆盖或忽略
最后更新:2023-11-15 05:30:34
|
状态:未完成
service.insert(table, set, new DefaultConfigStore().override(true, "ID")); service.insert(table, set, new DefaultConfigStore().override(false, "ID"));最终要生成的SQL:
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的关联条件，匹配成功表示数据已存在，执行WHEN MATCHED THEN分支 ，否则支持WHEN NOT MATCHED THEN分支
WHEN NOT MATCHED THEN 和WHEN MATCHED THEN 可以只用一个
