DB2使用INNER JOIN执行UPDATE报错的正确实现方法

DB2 原生不支持 MySQL 风格的 UPDATE 后直接接 INNER JOIN 多表关联的语法，同时 DB2 的 UPDATE 语句不支持LIMIT关键字，这两个点是当前语句报语法错误的直接原因。

需要的「多表关联匹配后更新目标表字段」需求，在DB2中有两种稳定的实现方式，逻辑和原本写的INNER JOIN匹配规则完全一致。

方案1：UPDATE + 关联子查询（推荐） #

通过EXISTS子句判断多表关联匹配关系，SET子句中通过关联子查询取到要更新的目标值，DB2全版本兼容。
针对需求改好的语句如下：

UPDATE PRODUCTS.SKUS SK
SET SK.CATEGORY_ID = (
    SELECT PC.ID
    FROM PRODUCTS.BODY BD
    INNER JOIN LEGACY_SCHEMA.BODY_OLD BDC
        ON BD.BODY_CODE = BDC.BODYF 
    INNER JOIN PRODUCTS.CATEGORIES PC
        ON BDC.CATEGORY_IDENTIFIER = PC.CATEGORY_SHORT 
    INNER JOIN PRODUCTS.PARENTS PR
        ON PR.ID = BD.PARENT_ID
    WHERE SK.BODY_ID = BD.ID
      AND PR.PARENT_CODE = 123
    FETCH FIRST 1 ROW ONLY
)
WHERE EXISTS (
    SELECT 1
    FROM PRODUCTS.BODY BD
    INNER JOIN LEGACY_SCHEMA.BODY_OLD BDC
        ON BD.BODY_CODE = BDC.BODYF 
    INNER JOIN PRODUCTS.CATEGORIES PC
        ON BDC.CATEGORY_IDENTIFIER = PC.CATEGORY_SHORT 
    INNER JOIN PRODUCTS.PARENTS PR
        ON PR.ID = BD.PARENT_ID
    WHERE SK.BODY_ID = BD.ID
      AND PR.PARENT_CODE = 123
)
FETCH FIRST 8 ROW ONLY;

注意事项 #

• SET子句里加FETCH FIRST 1 ROW ONLY是为了避免多表关联出现一对多匹配时，子查询返回多行触发运行时错误，如果业务上能确认关联关系是严格一对一，可以删掉这行。
• 如果使用的是9.7之前的老版本DB2，不支持UPDATE语句末尾直接加FETCH FIRST 8 ROW ONLY限制更新行数，可以先通过子查询筛选出要更新的8条SKU主键再做更新，参考写法：

UPDATE PRODUCTS.SKUS SK
SET SK.CATEGORY_ID = (
    SELECT PC.ID
    FROM PRODUCTS.BODY BD
    INNER JOIN LEGACY_SCHEMA.BODY_OLD BDC
        ON BD.BODY_CODE = BDC.BODYF 
    INNER JOIN PRODUCTS.CATEGORIES PC
        ON BDC.CATEGORY_IDENTIFIER = PC.CATEGORY_SHORT 
    INNER JOIN PRODUCTS.PARENTS PR
        ON PR.ID = BD.PARENT_ID
    WHERE SK.BODY_ID = BD.ID
      AND PR.PARENT_CODE = 123
    FETCH FIRST 1 ROW ONLY
)
WHERE SK.ID IN (
    SELECT SK_TMP.ID
    FROM PRODUCTS.SKUS SK_TMP
    INNER JOIN PRODUCTS.BODY BD
        ON SK_TMP.BODY_ID = BD.ID 
    INNER JOIN LEGACY_SCHEMA.BODY_OLD BDC
        ON BD.BODY_CODE = BDC.BODYF 
    INNER JOIN PRODUCTS.CATEGORIES PC
        ON BDC.CATEGORY_IDENTIFIER = PC.CATEGORY_SHORT 
    INNER JOIN PRODUCTS.PARENTS PR
        ON PR.ID = BD.PARENT_ID
    WHERE PR.PARENT_CODE = 123
    FETCH FIRST 8 ROW ONLY
);

方案2：MERGE 语句实现 #

对于关联逻辑复杂的更新场景，用MERGE语句可读性更好，也能避免子查询返回多行的问题，参考写法：

MERGE INTO PRODUCTS.SKUS SK
USING (
    SELECT SK_TMP.ID AS SKU_ID, PC.ID AS TARGET_CATEGORY_ID
    FROM PRODUCTS.SKUS SK_TMP
    INNER JOIN PRODUCTS.BODY BD
        ON SK_TMP.BODY_ID = BD.ID 
    INNER JOIN LEGACY_SCHEMA.BODY_OLD BDC
        ON BD.BODY_CODE = BDC.BODYF 
    INNER JOIN PRODUCTS.CATEGORIES PC
        ON BDC.CATEGORY_IDENTIFIER = PC.CATEGORY_SHORT 
    INNER JOIN PRODUCTS.PARENTS PR
        ON PR.ID = BD.PARENT_ID
    WHERE PR.PARENT_CODE = 123
    FETCH FIRST 8 ROW ONLY
) AS MATCHED_DATA
ON SK.ID = MATCHED_DATA.SKU_ID
WHEN MATCHED THEN UPDATE SET SK.CATEGORY_ID = MATCHED_DATA.TARGET_CATEGORY_ID;

补充说明 #

原语句里的PR.PARENT_CODE IN (123)是单值匹配，直接写PR.PARENT_CODE = 123即可，不需要用IN语法。

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