Postgres SCD Type 1 program updating all rows instead of matching rows, unmatched rows will be updated as NULL

Question

Postgres SCD Type 1 program updating all rows instead of only to those matching rows, those unmatched row will be updated as blank.

-- Create table tableToBeUpdated
CREATE TABLE "TEST"."TABLETOBEUPDATED" (
    "Z" character varying(5),
    "ID" integer,
    "Y" integer
);

-- Insert data into tableToBeUpdated
INSERT INTO "TEST"."TABLETOBEUPDATED" ("Z", "ID", "Y")
VALUES
    ('one', 1, 50),
    ('two', 2, 30),
    ('three', 3, 30),
    ('four', 4, 60),
    ('five', 5, 70),
    ('six', 6, 80);
    
    
-- Create table tableB
CREATE TABLE "TEST"."TABLEB" (
    "ID" integer,
    "NEWY" integer
);

-- Insert data into tableB
INSERT INTO "TEST"."TABLEB" ("ID", "NEWY")
VALUES
    (1, 500),
    (2, 233),
    (3, 300),
    (4, 472),
    (5, 111);
    

UPDATE "TEST"."TABLETOBEUPDATED"
SET "Y" = (
    SELECT "NEWY"
    FROM "TEST"."TABLEB"
    WHERE "TABLETOBEUPDATED"."ID" = "TABLEB"."ID"
);

SELECT * FROM "TEST"."TABLETOBEUPDATED"

Run the above program will produce the following result.

Expected result should be as follow.

Appreciate if anyone could help to correct my Postgres SQL program above. Thank you in advance.

Answer 1

You can create a subquery for this process as follows.

UPDATE "TEST"."TABLETOBEUPDATED" 
SET "Y" = "x"."NEWY" 
FROM (
    SELECT "ID","NEWY"
    FROM "TEST"."TABLEB"  
) AS x
WHERE "TEST"."TABLETOBEUPDATED"."ID" = "x"."ID" and x."NEWY" is not null

Answer 2

Since you did not specify a where clause your update statement tells postgres to update every row to the result of the following sub-select. For item z=>'six' that query sets the value to null as a result of no matching row for the sub-select. You can either supply an appropriate where clause or you could use the result of that query as the leading column of the coalesce() function and supply the existing y value as the training column. See documentation. Your query then becomes: (see demo here)

update tabletobeupdated
set y = coalesce(
                  (select newy
                     from tableb
                    where tabletobeupdated.id = tableb.id
                  )
                  , y
                );