SQL: Updating 2 different columns in table from CTE condition

Hi I have a temp table with 2 different columns that I am trying to update from a CTE that has a boolean column ‘uninstalled’. As in:

--Temp table to be updated:

CREATE TABLE temp_customers (
report_date date,
app_installed bigint,
app_uninstalled bigint,
total_volume bigint,
);

--CTE to be used to update the temp table:

with CTE as (
select count(account_id) as total_accounts
from customer_account ca
where uninstalled = false
);

I then want to update the table ‘temp_customers’ above from the CTE and their respective columns (where uninstalled = true shall update column ‘app_uninstalled’ with the correct volume of accounts and where uninstalled = false from CTE should only update column ‘app_installed’ with its respective volume.) How can I create this unique update statement for the ‘temp_customers’ including the report_date as the current timestamp?

Answer

You probably want something like this:

CREATE TEMP TABLE temp_customers AS
SELECT current_date                                AS report_date
     , count(*) FILTER (WHERE uninstalled = false) AS app_installed
     , count(*) FILTER (WHERE uninstalled        ) AS app_uninstalled
     , count(*)                                    AS total_volume
FROM   customer_account;

No CTE, no UPDATE.
Depending on what you plan to do with the result you might not need the temp table, either …