Netezza数据库关联两表用CASE WHEN更新列的SQL报错求助

错误原因 #

报错Update canceled: attempt to update a target row with values from multiple join rows的核心是：Sales表中存在至少一行数据，通过id + store_id的关联条件匹配到了Transaction表的多条记录。Netezza无法确定应该用哪一条Transaction记录的retail值来计算INDICATOR，因此拒绝执行更新。

解决方案 #

需要先对Transaction表按关联键做聚合处理，确保每个id + store_id组合只返回一个确定的判断结果，再关联Sales表进行更新。以下是几种常见业务逻辑对应的SQL：

1. 取对应Transaction记录中retail的最大值判断 #

如果需要用匹配到的最大retail值来决定INDICATOR：

UPDATE Sales s
SET indicator = CASE
    WHEN t.max_retail >= s.PRICE THEN 'Y'
    WHEN t.max_retail < s.PRICE THEN 'N'
    ELSE NULL
END
FROM (
    SELECT id, store_id, MAX(retail) AS max_retail
    FROM TRANSACTION
    GROUP BY id, store_id
) t
WHERE s.id = t.id AND s.STORE_ID = t.store_id;

2. 取对应Transaction记录中retail的最小值判断 #

如果需要用匹配到的最小retail值来决定INDICATOR：

UPDATE Sales s
SET indicator = CASE
    WHEN t.min_retail >= s.PRICE THEN 'Y'
    WHEN t.min_retail < s.PRICE THEN 'N'
    ELSE NULL
END
FROM (
    SELECT id, store_id, MIN(retail) AS min_retail
    FROM TRANSACTION
    GROUP BY id, store_id
) t
WHERE s.id = t.id AND s.STORE_ID = t.store_id;

3. 只要有任意一条Transaction记录的retail>=PRICE就设为Y #

只要匹配到的Transaction里有一行满足retail>=PRICE，就把INDICATOR设为Y，否则N：

UPDATE Sales s
SET indicator = CASE
    WHEN t.has_qualified = 1 THEN 'Y'
    ELSE 'N'
END
FROM (
    SELECT id, store_id,
           CASE WHEN MAX(CASE WHEN retail >= s.PRICE THEN 1 ELSE 0 END) = 1 THEN 1 ELSE 0 END AS has_qualified
    FROM TRANSACTION
    GROUP BY id, store_id
) t
WHERE s.id = t.id AND s.STORE_ID = t.store_id;

4. 所有Transaction记录的retail都>=PRICE才设为Y #

只有当匹配到的所有Transaction记录的retail都>=PRICE时，才设为Y，否则N：

UPDATE Sales s
SET indicator = CASE
    WHEN t.all_qualified = 1 THEN 'Y'
    ELSE 'N'
END
FROM (
    SELECT id, store_id,
           CASE WHEN MIN(CASE WHEN retail >= s.PRICE THEN 1 ELSE 0 END) = 1 THEN 1 ELSE 0 END AS all_qualified
    FROM TRANSACTION
    GROUP BY id, store_id
) t
WHERE s.id = t.id AND s.STORE_ID = t.store_id;

注意事项 #

• 具体选择哪种聚合逻辑，需根据实际业务需求确定
• 如果要处理Sales表中无匹配Transaction记录的行，可在CASE语句的ELSE分支补充对应逻辑，比如设为特定标识或NULL

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