How to insert field from a subquery

How can I insert a field from a subquery. The code is below. I want to add in the date of birth for the oldest child on each policy. I am getting an error saying I can't insert null values into the target table. I tried to alter the column to accept null values but that didn't work either. If you have any other ideas to do this insert into the target table I would really appreciate the help!

Insert into JOURNEY_1A t (oldestchild_dob)
select A.oldestchild_dob
FROM
(
select DISTINCT T.POLICY_ID, max( M.ME_BIRTH_DATE) AS OLDESTCHILD_DOB
from JOURNEY_1A t, ALL_MTH S, member M
WHERE T.POLICY_ID = S.POLICY_ID
AND S.MONTH_ID = (SELECT MAX(MONTH_ID) FROM ALL_MTH )
AND S.MEMBER_ID = M.MEMBER_ID
AND trunc((to_date(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
JOIN JOURNEY_1A T
ON A.POLICY_ID = T.POLICY_ID
;

Solution

You appear to want a correlated UPDATE and not an INSERT as you are trying to modify (update) a value in an existing row and not create (insert) a new row.

UPDATE JOURNEY_1A t 
SET  oldestchild_dob = ( SELECT max( M.ME_BIRTH_DATE)
                         FROM   ALL_MTH S
                                INNER JOIN member M
                                ON S.MEMBER_ID = M.MEMBER_ID
                         WHERE  T.POLICY_ID = S.POLICY_ID
                         AND    S.MONTH_ID = (SELECT MAX(MONTH_ID) FROM ALL_MTH )
                         AND    m.me_date_of_birth > ADD_MONTHS(TRUNC(SYSDATE), -18*12)
                       );

Note: untested as we do not have your tables.

Note: the M.ME_BIRTH_DATE IS NOT NULL filter is not necessary as you already test that M.ME_BIRTH_DATE is in the last 18 years and cannot be NULL if it matches that filter.

Note: Do not try to calculate age using 365.25 days-per-year as you will get edge-cases where the calculation includes or excludes someone who is almost 18 when it should not. Use ADD_MONTHS to subtract 18 years from today and use that as the boundary (or use MONTHS_BETWEEN).

Your code does not work because INSERT will try to insert a new row (it does not insert a new value into an existing row) and because you are only specifying a single column then the database will use the default value for all the other columns of that row. If you have not specified a default value then NULL is used.

This means that:

Insert into JOURNEY_1A t (oldestchild_dob) VALUES (DATE '2023-01-01');

is the same as:

Insert into JOURNEY_1A t (policy_id, col1, col2, oldestchild_dob)
  VALUES (DEFAULT, DEFAULT, DEFAULT, DATE '2023-01-01');

which is probably going to be resolved as:

Insert into JOURNEY_1A t (policy_id, col1, col2, oldestchild_dob)
  VALUES (NULL, NULL, NULL, DATE '2023-01-01');

and the NULL value issue is for all the columns you do not specify, and not the one column that you do specify.

If you do want to INSERT a new row then you will need to specify values for all the other columns in the table (unless you intend them to be the default, probably NULL, value).