Oracle 19单查询实现基于EnrollmentTransaction的EnrollmentDetail增改

现有表数据 #

表 EnrollmentTransaction #

EnrollmentId | TransactionId
-------------+--------------
5            | 1
5            | 2
6            | 3
7            | 2
8            | 3
8            | 2
8            | 1

表 EnrollmentDetail（新增TransactionId列，默认值为1） #

EnrollmentId | ParameterId| TransactionId 
-------------+------------+---------------
5            | 1          | 1
6            | 8          | 1
7            | 9          | 1
7            | 6          | 1
8            | 8          | 1

需求 #

基于EnrollmentId关联两张表，对EnrollmentDetail执行：

• 插入：当EnrollmentId对应的TransactionId组合未在表中存在时，新增记录（如EnrollmentId=5需插入(5,1,2)）
• 更新：当EnrollmentId在EnrollmentTransaction中仅有1条TransactionId时，更新表中对应行的TransactionId（如EnrollmentId=6需更新为(6,8,3)）

EnrollmentDetail的主键为(EnrollmentId, ParameterId, TransactionId)，预期最终结果：

EnrollmentId | ParameterId| TransactionId 
-------------+------------+---------------
5            | 1          | 1
5            | 1          | 2
6            | 8          | 3
7            | 9          | 2
7            | 6          | 2
8            | 8          | 1
8            | 8          | 2
8            | 8          | 3

解决方案 #

可以用Oracle的MERGE语句实现单条SQL完成需求，具体代码如下：

MERGE INTO EnrollmentDetail ed
USING (
    -- 生成所有需要的(EnrollmentId, ParameterId, TransactionId)组合
    SELECT 
        et.EnrollmentId,
        ed_orig.ParameterId,
        et.TransactionId
    FROM EnrollmentTransaction et
    INNER JOIN (
        -- 获取原表中每个EnrollmentId对应的所有ParameterId
        SELECT DISTINCT EnrollmentId, ParameterId 
        FROM EnrollmentDetail
    ) ed_orig 
        ON et.EnrollmentId = ed_orig.EnrollmentId
) src
ON (
    ed.EnrollmentId = src.EnrollmentId
    AND ed.ParameterId = src.ParameterId
    AND ed.TransactionId = src.TransactionId
)
WHEN MATCHED THEN
    -- 仅更新初始默认值为1的行，替换为表1中的对应TransactionId
    UPDATE SET ed.TransactionId = src.TransactionId
    WHERE ed.TransactionId = 1
WHEN NOT MATCHED THEN
    -- 插入原表中不存在的目标组合
    INSERT (EnrollmentId, ParameterId, TransactionId)
    VALUES (src.EnrollmentId, src.ParameterId, src.TransactionId);

逻辑说明 #

1. 子查询src：关联EnrollmentTransaction和EnrollmentDetail的去重参数集，生成所有需要存在的记录组合，这是我们的目标数据集合。
2. 匹配条件：通过三主键判断记录是否已存在于EnrollmentDetail。
3. 更新逻辑：只针对原表中TransactionId为默认值1的行进行更新，避免覆盖已存在的有效数据。
4. 插入逻辑：将目标数据集中未在原表出现的记录插入。

执行该语句后，EnrollmentDetail将完全符合预期结果。

内容的提问来源于stack exchange，提问作者Seegel