SQL UPDATE语句中单行子查询返回多行的问题排查与解决

问题原因 #

尽管PIDM是唯一标识，但以下情况会导致子查询返回多行：

• 关联的GENERAL.GORSRVR或GENERAL.GOBSRVR表中，存在多条符合WHERE条件的记录。比如同一个PIDM对应多条GOBSRVR_NAME = 'SB564'且GOBSRVR_COMPLETE_IND = 'Y'的记录，或者GORSRVR中同一个PIDM、GORSRVR_QUESTION_NO = 1的记录有多条。
• 原SQL使用隐式连接，加上GORSRVR_NAME = GOBSRVR_NAME的条件，若两个表存在多对多关联，会产生笛卡尔积，进一步导致返回多行。

解决方案 #

方案1：用聚合函数确保返回单行 #

通过MAX()或MIN()聚合函数，即使有多条符合条件的记录，也只会返回一个结果（CASE语句返回的是同一逻辑下的结果，聚合不影响最终值）：

UPDATE SCARF.STUDENT
SET IS_PARENT =
(SELECT 
       MAX(CASE WHEN GORSRVR_RESPONSE_1 IS NOT NULL THEN 'N'
                WHEN GORSRVR_RESPONSE_2 IS NOT NULL THEN 'YS'
                WHEN GORSRVR_RESPONSE_3 IS NOT NULL THEN 'YA'
                WHEN GORSRVR_RESPONSE_4 IS NOT NULL THEN 'YB'
           END)
FROM 
    GENERAL.GORSRVR
JOIN GENERAL.GOBSRVR 
  ON GORSRVR_PIDM = GOBSRVR_PIDM
WHERE 
    GORSRVR_PIDM = student.MPIDM
    AND GOBSRVR_NAME = 'SB564'
    AND GORSRVR_NAME = GOBSRVR_NAME
    AND GOBSRVR_COMPLETE_IND = 'Y'
    AND GORSRVR_QUESTION_NO = 1);

方案2：限制返回行数 #

根据数据库类型，用ROWNUM（Oracle）或LIMIT（MySQL/PostgreSQL）强制子查询只返回第一行：

Oracle版本：

UPDATE SCARF.STUDENT
SET IS_PARENT =
(SELECT 
       CASE WHEN GORSRVR_RESPONSE_1 IS NOT NULL THEN 'N'
                WHEN GORSRVR_RESPONSE_2 IS NOT NULL THEN 'YS'
                WHEN GORSRVR_RESPONSE_3 IS NOT NULL THEN 'YA'
                WHEN GORSRVR_RESPONSE_4 IS NOT NULL THEN 'YB'
           END
FROM 
    GENERAL.GORSRVR
JOIN GENERAL.GOBSRVR 
  ON GORSRVR_PIDM = GOBSRVR_PIDM
WHERE 
    GORSRVR_PIDM = student.MPIDM
    AND GOBSRVR_NAME = 'SB564'
    AND GORSRVR_NAME = GOBSRVR_NAME
    AND GOBSRVR_COMPLETE_IND = 'Y'
    AND GORSRVR_QUESTION_NO = 1
AND ROWNUM = 1);

MySQL/PostgreSQL版本：

UPDATE SCARF.STUDENT
SET IS_PARENT =
(SELECT 
       CASE WHEN GORSRVR_RESPONSE_1 IS NOT NULL THEN 'N'
                WHEN GORSRVR_RESPONSE_2 IS NOT NULL THEN 'YS'
                WHEN GORSRVR_RESPONSE_3 IS NOT NULL THEN 'YA'
                WHEN GORSRVR_RESPONSE_4 IS NOT NULL THEN 'YB'
           END
FROM 
    GENERAL.GORSRVR
JOIN GENERAL.GOBSRVR 
  ON GORSRVR_PIDM = GOBSRVR_PIDM
WHERE 
    GORSRVR_PIDM = student.MPIDM
    AND GOBSRVR_NAME = 'SB564'
    AND GORSRVR_NAME = GOBSRVR_NAME
    AND GOBSRVR_COMPLETE_IND = 'Y'
    AND GORSRVR_QUESTION_NO = 1
LIMIT 1);

方案3：排查并清理重复数据 #

先查询确认哪些PIDM对应多条符合条件的记录：

SELECT GORSRVR_PIDM, COUNT(*)
FROM GENERAL.GORSRVR
JOIN GENERAL.GOBSRVR 
  ON GORSRVR_PIDM = GOBSRVR_PIDM
WHERE 
    GOBSRVR_NAME = 'SB564'
    AND GORSRVR_NAME = GOBSRVR_NAME
    AND GOBSRVR_COMPLETE_IND = 'Y'
    AND GORSRVR_QUESTION_NO = 1
GROUP BY GORSRVR_PIDM
HAVING COUNT(*) > 1;

根据查询结果，清理重复记录或调整WHERE条件（比如添加时间字段取最新记录），从根源解决问题。

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