SQL state = 23502 when using UPDATE command

Question

I am using QMF for windows. My code looks like this:

UPDATE PIDJBIP.JBI_SSS_MASTER_T SSS
SET SSS.NATIONAL_ACCT_NAME = (
SELECT GOLD.ACCT_NAME
FROM PIDJBIP.THE_GOLDEN_STANDARD GOLD
WHERE SSS.NATIONAL_ACCT_NBR = GOLD.ACCT_NBR AND GOLD.ACCT_NAME IS NOT NULL )

I want to set the value of my NATIONAL_ACCT_NAME column in the master table to the value of my ACCT_NAME value in the golden standard table only when I find matching account numbers. The idea is that I won't lose any account names currently in the master table unless I have a replacement account name for them in the golden standard table. The problem is when I run the query above I get the SQL state = 23502 SQL code =-407 error. It says an assignment of a NULL value to a NOT NULL column is not allowed.

A couple of things confuse me: I have checked my golden standard table for any null values and it does not contain any. So, it shouldn't be trying to set any account names to null in the master table. Also, after getting this error originally I added the constraint to my query that gold.acct_name is not null so there should be no possible way that it is trying to set any values in the master table to null.

Could someone help explain what is happening here?

Answer 1

As Gordon Linoff suggested, you are getting these errors for rows in JBI_SSS_MASTER_T where there aren't matching rows in THE_GOLDEN_STANDARD: the subselect returns nulls for those.

You'll want to restrict the update to those rows only that have matches:

UPDATE PIDJBIP.JBI_SSS_MASTER_T SSS
SET SSS.NATIONAL_ACCT_NAME = (
  SELECT GOLD.ACCT_NAME
  FROM PIDJBIP.THE_GOLDEN_STANDARD GOLD
  WHERE SSS.NATIONAL_ACCT_NBR = GOLD.ACCT_NBR AND GOLD.ACCT_NAME IS NOT NULL )
WHERE EXISTS (SELECT 1
  FROM PIDJBIP.THE_GOLDEN_STANDARD GOLD
  WHERE SSS.NATIONAL_ACCT_NBR = GOLD.ACCT_NBR AND GOLD.ACCT_NAME IS NOT NULL)