I am running this in PostgreSQL 14, in the query tool. Basically I want to create a new column testcol, and populate it with the result of another query (put the value of CITIES_BIGGER_THAN_1M in testcol if city_id is the same, otherwise leave it at null). Here is the code:

ALTER TABLE cities ADD COLUMN IF NOT EXISTS testcol INT;

WITH CITIES_BIGGER_THAN_1M AS (
    SELECT city_id, population/1000000 as pop_in_m
    FROM cities WHERE population >= 1000000
)
UPDATE cities 
SET testcol = CITIES_BIGGER_THAN_1M.pop_in_m
FROM CITIES_BIGGER_THAN_1M
WHERE cities.city_id = CITIES_BIGGER_THAN_1M.city_id;
-- OUTPUT:
-- NOTICE:  column "testcol" of relation "cities" already exists, skipping
-- UPDATE 770

-- Query returned successfully in 163 msec.

SELECT * FROM cities WHERE testcol != NULL
--OUTPUT: zero rows...