如何从子查询插入字段？遇非空约束报错求解决方案

你的核心问题是误用了INSERT语句——INSERT是用来新增表记录的，而你实际需求是给现有保单的oldestchild_dob字段赋值。原SQL执行时，会插入大量仅oldestchild_dob有值、其他字段全为NULL的新记录，如果表中其他字段有非空约束，就会触发"无法插入NULL值"的报错，哪怕你设置oldestchild_dob允许NULL也没用，因为报错根源是其他必填字段为空。

正确实现：用UPDATE更新现有记录 #

改用UPDATE语句，结合关联子查询直接更新目标表的字段值：

UPDATE JOURNEY_1A t
SET t.oldestchild_dob = (
    -- 子查询获取当前保单对应年龄最大的子女出生日期
    SELECT MAX(m.ME_BIRTH_DATE)
    FROM ALL_MTH s
    JOIN member m ON s.MEMBER_ID = m.MEMBER_ID
    WHERE s.POLICY_ID = t.POLICY_ID
      AND s.MONTH_ID = (SELECT MAX(MONTH_ID) FROM ALL_MTH)
      AND TRUNC((SYSDATE - m.ME_BIRTH_DATE)/365.25) BETWEEN 0 AND 17
      AND m.ME_BIRTH_DATE IS NOT NULL
)
-- 可选：仅更新有符合条件子女的保单，避免无子女保单的字段被设为NULL
WHERE EXISTS (
    SELECT 1
    FROM ALL_MTH s
    JOIN member m ON s.MEMBER_ID = m.MEMBER_ID
    WHERE s.POLICY_ID = t.POLICY_ID
      AND s.MONTH_ID = (SELECT MAX(MONTH_ID) FROM ALL_MTH)
      AND TRUNC((SYSDATE - m.ME_BIRTH_DATE)/365.25) BETWEEN 0 AND 17
      AND m.ME_BIRTH_DATE IS NOT NULL
);

补充说明 #

1. 原SQL中的DISTINCT是多余的：GROUP BY t.policy_id已经会对保单ID去重，无需额外加DISTINCT。
2. 如果需要同时处理"新增保单+更新现有保单"的场景，可以用MERGE语句：

MERGE INTO JOURNEY_1A t
USING (
    SELECT 
        t.POLICY_ID,
        MAX(m.ME_BIRTH_DATE) AS OLDESTCHILD_DOB
    FROM JOURNEY_1A t
    JOIN ALL_MTH s ON t.POLICY_ID = s.POLICY_ID
    JOIN member m ON s.MEMBER_ID = m.MEMBER_ID
    WHERE s.MONTH_ID = (SELECT MAX(MONTH_ID) FROM ALL_MTH)
      AND TRUNC((SYSDATE - m.ME_BIRTH_DATE)/365.25) BETWEEN 0 AND 17
      AND m.ME_BIRTH_DATE IS NOT NULL
    GROUP BY t.POLICY_ID
) a ON (t.POLICY_ID = a.POLICY_ID)
WHEN MATCHED THEN 
    UPDATE SET t.oldestchild_dob = a.OLDESTCHILD_DOB
WHEN NOT MATCHED THEN 
    INSERT (POLICY_ID, oldestchild_dob) -- 需补充其他非空字段的值
    VALUES (a.POLICY_ID, a.OLDESTCHILD_DOB);

注意：WHEN NOT MATCHED分支必须提供表中所有非空字段的值，否则同样会触发NULL插入报错。

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