如何在DB2的UPDATE语句中使用JOIN实现数据更新

由于DB2不支持UPDATE语句中直接使用JOIN，你可以通过CTE结合UPDATE或者MERGE语句来实现需求，以下是两种可行方案：

方案1：CTE+UPDATE（推荐，逻辑清晰） #

先通过CTE筛选出所有需要更新的目标记录和对应的新值，再执行更新：

WITH UPDATE_DATA AS (
    SELECT 
        MVM.ROWID AS TARGET_ROW, -- 用ROWID唯一标识FILE2的待更新记录
        ADT.QTY AS NEW_QTY
    FROM 
        -- 原查询中的ADT子查询：FILE1的筛选和排名
        (SELECT 
             A.*, 
             RANK() OVER (PARTITION BY A.SO, A.ST, A.PA, A.DC ORDER BY A.AM) AS RANK 
         FROM FILE1 A 
         WHERE A.TR IN ('73', '74') AND A.LS >= '2023152') ADT
    JOIN 
        -- 原查询中的MVM子查询：FILE2的筛选和排名
        (SELECT 
             A.*, 
             RANK() OVER (PARTITION BY A.SO, A.ST, A.PA, A.RE ORDER BY A.AD) AS RANK 
         FROM FILE2 A 
         WHERE A.TR = '06' AND A.AD >= '2023152') MVM 
        ON ADT.SO = MVM.SO 
           AND ADT.ST = MVM.ST 
           AND ADT.PA = MVM.PA 
           AND ADT.DC = MVM.RE 
           AND ADT.RANK = MVM.RANK
    WHERE 
        MVM.AD <> ADT.AM
        AND TRIM(MVM.ST) || TRIM(MVM.SO) || TRIM(MVM.PA) || TRIM(MVM.RE) IN (
            SELECT DISTINCT TRIM(ST) || TRIM(SO) || TRIM(PA) || TRIM(DC) 
            FROM FILE1 
            WHERE TR IN ('73E','74E') AND LS >= '2023152'
        )
)
-- 更新FILE2中匹配的记录
UPDATE FILE2
SET QTY = (SELECT NEW_QTY FROM UPDATE_DATA WHERE UPDATE_DATA.TARGET_ROW = FILE2.ROWID)
WHERE EXISTS (
    SELECT 1 FROM UPDATE_DATA WHERE UPDATE_DATA.TARGET_ROW = FILE2.ROWID
);

说明： #

• ROWID是DB2中用于唯一标识表中每行的系统字段（适用于DB2 LUW），如果是DB2 for i，可以用RID()函数替代，或者直接使用FILE2的主键组合（如SO、ST、PA、RE等能唯一确定行的字段）来代替ROWID。
• CTE UPDATE_DATA 完全复用了你原查询的筛选和关联逻辑，确保只更新符合条件的记录。

方案2：使用MERGE语句 #

MERGE是DB2专门用于合并数据的语句，支持匹配目标表和源数据集进行更新：

MERGE INTO FILE2 TGT
USING (
    SELECT 
        ADT.SO, ADT.ST, ADT.PA, ADT.DC, ADT.QTY, ADT.AM,
        ADT.RANK AS ADT_RANK,
        MVM.AD, MVM.RE, MVM.SO AS MVM_SO, MVM.ST AS MVM_ST, MVM.PA AS MVM_PA
    FROM 
        (SELECT 
             A.*, 
             RANK() OVER (PARTITION BY A.SO, A.ST, A.PA, A.DC ORDER BY A.AM) AS RANK 
         FROM FILE1 A 
         WHERE A.TR IN ('73', '74') AND A.LS >= '2023152') ADT
    JOIN FILE2 MVM
        ON ADT.SO = MVM.SO 
           AND ADT.ST = MVM.ST 
           AND ADT.PA = MVM.PA 
           AND ADT.DC = MVM.RE
    WHERE 
        MVM.TR = '06' AND MVM.AD >= '2023152'
        AND MVM.AD <> ADT.AM
        AND TRIM(MVM.ST) || TRIM(MVM.SO) || TRIM(MVM.PA) || TRIM(MVM.RE) IN (
            SELECT DISTINCT TRIM(ST) || TRIM(SO) || TRIM(PA) || TRIM(DC) 
            FROM FILE1 
            WHERE TR IN ('73E','74E') AND LS >= '2023152'
        )
) SRC
ON (
    TGT.SO = SRC.MVM_SO
    AND TGT.ST = SRC.MVM_ST
    AND TGT.PA = SRC.MVM_PA
    AND TGT.RE = SRC.DC
    AND TGT.AD = SRC.AD
    -- 匹配排名条件
    AND RANK() OVER (PARTITION BY TGT.SO, TGT.ST, TGT.PA, TGT.RE ORDER BY TGT.AD) = SRC.ADT_RANK
)
WHEN MATCHED THEN
    UPDATE SET TGT.QTY = SRC.QTY;

说明： #

• MERGE语句直接将FILE2作为目标表，源数据集包含了FILE1的匹配数据和FILE2的必要字段。
• 由于FILE2本身没有存储RANK值，需要在ON条件中动态计算目标行的RANK来匹配源数据的排名。

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