Oracle 19c：如何用单查询基于映射表更新TransactionId

现有表结构及数据 #

1. ProductTransaction表（主键为ProductId、TransactionId） #

每个ProductId的TransactionId仅存在三种组合：1/2/3单独、1和2、1/2/3全部，数据如下：

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

2. ProductRange表（主键为ProductRangeId、ProductId、TransactionId） #

TransactionId为新增非空列，默认值为1，初始数据如下：

ProductRangeId| ProductId  | TransactionId 
-------------+------------ +--------------
31            | 5          | 1
32            | 6          | 1
33            | 7          | 1
34            | 7          | 1
35            | 7          | 1
36            | 8          | 1
37            | 9          | 1
38            | 9          | 1

更新规则 #

• 若ProductId在ProductTransaction中仅一条记录：将ProductRange中对应ProductId的所有记录的TransactionId更新为该唯一值；
• 若ProductId在ProductTransaction中有多条记录：
  • 组合为1和2时，更新为4；
  • 组合为1/2/3全部时，更新为5；

期望更新结果 #

ProductRangeId| ProductId  | TransactionId 
-------------+------------ +--------------
31            | 5          | 4
32            | 6          | 3
33            | 7          | 4
34            | 7          | 4
35            | 7          | 4
36            | 8          | 5
37            | 9          | 1
38            | 9          | 1

问题解答：可以用单SQL实现，推荐使用MERGE语句 #

在Oracle 19c中，可通过MERGE语句结合分组统计实现一次性更新，具体SQL如下：

MERGE INTO ProductRange pr
USING (
    SELECT 
        pt.ProductId,
        CASE 
            WHEN COUNT(pt.TransactionId) = 1 THEN MAX(pt.TransactionId)
            WHEN EXISTS (SELECT 1 FROM ProductTransaction pt2 WHERE pt2.ProductId = pt.ProductId AND pt2.TransactionId = 3) THEN 5
            ELSE 4
        END AS NewTransactionId
    FROM ProductTransaction pt
    GROUP BY pt.ProductId
) t
ON (pr.ProductId = t.ProductId)
WHEN MATCHED THEN
    UPDATE SET pr.TransactionId = t.NewTransactionId;

逻辑说明 #

1. 子查询对ProductTransaction按ProductId分组，计算每个ProductId对应的目标TransactionId：
   • 单条记录时，直接取该唯一的TransactionId；
   • 多条记录时，检查是否包含TransactionId=3，包含则设为5，否则设为4；
2. MERGE语句关联ProductRange和子查询结果，匹配到的记录直接更新TransactionId；
3. 对于ProductId=9的情况，因ProductTransaction中仅一条值为1的记录，更新后保持1，符合预期。

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