skip to Main Content

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.

Row number 6, Y has been updated as NULL

Expected result should be as follow.

Intended result

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

2

Answers


  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
    
    Login or Signup to reply.
  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
                    );       
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search