Updating a column from the results of a counting subquery

Question

I have the following query:

SELECT count(distinct document_key), etl_telco_cycle.customer_number FROM telco_document_header inner join etl_telco_cycle on  (telco_document_header.customer_number like '%' || etl_telco_cycle.customer_number) where telco_document_header.document_cycle = substring(cast(now() - interval '1 month' as varchar) from 1 for 4) || substring(cast(now() - interval '1 month' as varchar) from 6 for 2) and telco_document_header.customer_number like '%' || etl_telco_cycle.customer_number) group by etl_telco_cycle.customer_number

which returns this:

Now I want to use that result to update count in a table where customer_number match. I tried this:

update etl_telco_cycle set amount_mobilephone_numbers = (SELECT count(distinct document_key), etl_telco_cycle.customer_number FROM telco_document_header inner join etl_telco_cycle on  (telco_document_header.customer_number like '%' || etl_telco_cycle.customer_number) where telco_document_header.document_cycle = substring(cast(now() - interval '1 month' as varchar) from 1 for 4) || substring(cast(now() - interval '1 month' as varchar) from 6 for 2) group by etl_telco_cycle.customer_number)

which results in this:

Please remember to always disclose your version of Postgres. And for data and error messages we very much prefer text over screenshots. — Erwin Brandstetter, Nov 07 '18 at 16:15

Erwin Brandstetter · Answer 1 · 2018-11-08T12:35:07.860

Use the FROM clause to the UPDATE command:

UPDATE etl_telco_cycle e
SET    amount_mobilephone_numbers = c.ct
FROM  (
   SELECT e.customer_number, count(distinct document_key) AS ct
   FROM   telco_document_header t
   JOIN   etl_telco_cycle       e ON  t.customer_number like '%' || e.customer_number
   WHERE  t.document_cycle = substring(cast(now() - interval '1 month' as varchar) from 1 for 4)
                          || substring(cast(now() - interval '1 month' as varchar) from 6 for 2)
   GROUP  BY 1
   ) c
WHERE e.customer_number = c.customer_number
AND   e.amount_mobilephone_numbers IS DISTINCT FROM c.ct;  --optional optimization

While you can also use a correlated subquery, this would typically be much slower, running one aggregation query per target row, while this query runs a single aggregation query. And there is a minor difference: if no related rows are found in a correlated subquery like Gordon demonstrates, the column is still updated to NULL (which would fail for columns defined NOT NULL), while my query does nothing instead (keeping the old value). You'll have to define the desired behavior.

The added AND e.amount_mobilephone_numbers IS DISTINCT FROM c.ct prevents empty updates. Related:

How do I (or can I) SELECT DISTINCT on multiple columns?

You could optimize the performance of the counting subquery some more. You may not need DISTINCT nor the JOIN in the subquery - would need to see exact table definitions and constraits. Looks like you can replace this either way:

   substring(cast(now() - interval '1 month' as varchar) from 1 for 4)
|| substring(cast(now() - interval '1 month' as varchar) from 6 for 2)

with:

to_char(now() - interval '1 month', 'YYYYMM')

Either depends on the current timezone setting, which may be undesirable in corner cases.

And document_cycle should be a date or integer, not a string type ...

@HeikoO: Note the additions. – Erwin Brandstetter Nov 08 '18 at 12:35 — Erwin Brandstetter, Nov 08 '18 at 12:35

score 1 · Accepted Answer · answered Nov 07 '18 at 16:38

You can just use a correlated subquery:

update etl_telco_cycle
    set amount_mobilephone_numbers = (SELECT count(distinct document_key)
                                      FROM telco_document_header tdh
                                      WHERE tdh.customer_number = etl_telco_cycle.customer_number AND 
                                            tdh.document_cycle = substring(cast(now() - interval '1 month' as varchar) from 1 for 4) || substring(cast(now() - interval '1 month' as varchar) from 6 for 2) 
                                    );

I'm not sure why your version uses LIKE for the match on customer numbers. That seems awkward, so I removed it.

I also think the date logic can be written more concisely using TO_CHAR():

update etl_telco_cycle
    set amount_mobilephone_numbers = (SELECT count(distinct document_key)
                                      FROM telco_document_header tdh
                                      WHERE tdh.customer_number = etl_telco_cycle.customer_number AND 
                                            tdh.document_cycle = TO_CHAR(now() - interval '1 month', 'YYYYDD')
                                     );

Updating a column from the results of a counting subquery

2 Answers2