Aggregate update-from-select won't update null values

Question

I am adding a not-null column to an existing table. I already added the nullable column. Then I want to compute the correct values for existing rows.

I have the following query

UPDATE FTT_REGISTRY_REPORT REP
SET COUNT_491 = SUBQUERY.CNT
FROM (
    SELECT COALESCE(COUNT(*), 0) AS CNT, REGISTRY_ID,   SOURCE
    FROM
        FTT_TX_491 OP
    GROUP BY
        REGISTRY_ID,SOURCE
    ) AS SUBQUERY
WHERE
    REP.SOURCE = SUBQUERY.SOURCE
    AND REP.REGISTRY_ID = SUBQUERY.REGISTRY_ID;

Where FTT_REGISTRY_REPORT has unique index over REGISTRY_ID and SOURCE. I basically want that for each existent record in FTT_REGISTRI_REPORT a COUNT query is executed in the raw table and the number of records matching the same source and registry id is replaced into the report column.

There may be no record matching an existing FTT_REGISTRY_REPORT row. It is out of scope to discuss why the row self exists (I collect statistics on 12 queries on 12 different columns). In that case, I cleverly tried to use COALESCE.

However, after the query execution a lot of rows for which no corresponding record exists in FTT_TX_491 table I get null values, so that I can't enable not null constraint. Not because the query is returning null, but because the rows are not getting update. See the workaround.

I would like to understand why my coalesce function is not working. ideally, the subquery should be counting zero, or null, which is coalesced to zero, when no matching row exists.

Workaround

I managed to fix my issue, but still would love to understand why it's not working, by coercing all rows to 0 first

UPDATE FTT_REGISTRY_REPORT SET COUNT_491=0;
-- My previous query

Gordon Linoff Gordon Linoff · Accepted Answer · 2019-06-27T15:11:14

You can use a correlated subquery:

UPDATE FTT_REGISTRY_REPORT REP
    SET COUNT_491 = (SELECT COUNT(*)
                     FROM FTT_TX_491 OP
                     WHERE REP.SOURCE = SUBQUERY.SOURCE AND
                           REP.REGISTRY_ID = SUBQUERY.REGISTRY_ID
                    );

The subquery will always return a value. The count will be zero if no rows match.

Another method would be to define the column as NOT NULL DEFAULT 0. This will create the column with zeros and then you can just update the rows with other values.

Aggregate update-from-select won't update null values

1 Answers