Oracle中使用FULL OUTER JOIN和子查询批量更新失败问题

我有如下查询语句：

SELECT DISTINCT EPS_PROPOSAL.PROPOSAL_NUMBER FROM PROP_ADMIN, EPS_PROPOSAL
    FULL OUTER JOIN PROP_ADMIN 
    ON EPS_PROPOSAL.PROPOSAL_NUMBER = PROP_ADMIN.PROPOSAL_NUMBER
    WHERE EPS_PROPOSAL.SPONSOR_CODE = 100728 AND 
        (EPS_PROPOSAL.STATUS_CODE = 3 OR EPS_PROPOSAL.STATUS_CODE = 6)

该查询返回339条PROPOSAL_NUMBER记录，对应的PROP_ADMIN表中FUNDING_CODE均为null：

PROPOSAL_NUMBER    FUNDING_CODE
    4214              (null)
    3079              (null)
    3212              (null)
      .                  .
      .                  .
 TOTAL RECORDS: 339

我尝试使用上述WHERE条件和OUTER JOIN，将PROP_ADMIN表中这些记录的FUNDING_CODE更新为'F'，执行的UPDATE语句如下：

UPDATE PROP_ADMIN
    SET FUNDING_CODE = 'F'
    WHERE PROPOSAL_NUMBER IN(
       SELECT DISTINCT EPS_PROPOSAL.PROPOSAL_NUMBER FROM PROP_ADMIN, EPS_PROPOSAL
       FULL OUTER JOIN PROP_ADMIN 
       ON EPS_PROPOSAL.PROPOSAL_NUMBER = PROP_ADMIN.PROPOSAL_NUMBER
           WHERE EPS_PROPOSAL.SPONSOR_CODE = 100728 AND 
           (EPS_PROPOSAL.STATUS_CODE = 3 OR EPS_PROPOSAL.STATUS_CODE = 6)

但执行后仅更新了1条记录，其余符合条件的行未被更新，请问如何让UPDATE语句对所有符合条件的行生效？

原因分析 #

你的原语句存在两个核心问题：

1. 重复关联表：子查询里同时写了FROM PROP_ADMIN, EPS_PROPOSAL和FULL OUTER JOIN PROP_ADMIN，导致PROP_ADMIN被多次引用，产生错误的关联结果，让IN子查询返回的记录不符合预期。
2. FULL JOIN误用：你的需求是找到EPS_PROPOSAL中符合条件的记录，且对应PROP_ADMIN里FUNDING_CODE为null的行，FULL JOIN会包含两边表不匹配的记录，完全没必要，反而干扰了结果。

正确的UPDATE写法 #

方法1：简化IN子查询 #

先修正子查询，去掉重复的表引用，只保留必要的筛选逻辑：

UPDATE PROP_ADMIN
SET FUNDING_CODE = 'F'
WHERE PROPOSAL_NUMBER IN (
    SELECT DISTINCT EP.PROPOSAL_NUMBER
    FROM EPS_PROPOSAL EP
    WHERE EP.SPONSOR_CODE = 100728
      AND EP.STATUS_CODE IN (3, 6)
)
AND FUNDING_CODE IS NULL; -- 确保只更新原本为null的行，避免重复更新

方法2：使用JOIN更新（性能更优） #

多数数据库支持直接用JOIN关联更新，逻辑更清晰：

-- 适用于SQL Server、MySQL等数据库
UPDATE PA
SET PA.FUNDING_CODE = 'F'
FROM PROP_ADMIN PA
JOIN EPS_PROPOSAL EP ON PA.PROPOSAL_NUMBER = EP.PROPOSAL_NUMBER
WHERE EP.SPONSOR_CODE = 100728
  AND EP.STATUS_CODE IN (3, 6)
  AND PA.FUNDING_CODE IS NULL;

如果是Oracle数据库，语法调整为：

UPDATE PROP_ADMIN PA
SET PA.FUNDING_CODE = 'F'
WHERE EXISTS (
    SELECT 1
    FROM EPS_PROPOSAL EP
    WHERE EP.PROPOSAL_NUMBER = PA.PROPOSAL_NUMBER
      AND EP.SPONSOR_CODE = 100728
      AND EP.STATUS_CODE IN (3, 6)
)
AND PA.FUNDING_CODE IS NULL;

验证步骤 #

执行更新前，可以先运行以下查询确认要更新的记录数是否为339：

SELECT COUNT(*)
FROM PROP_ADMIN PA
JOIN EPS_PROPOSAL EP ON PA.PROPOSAL_NUMBER = EP.PROPOSAL_NUMBER
WHERE EP.SPONSOR_CODE = 100728
  AND EP.STATUS_CODE IN (3, 6)
  AND PA.FUNDING_CODE IS NULL;

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