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.
2
Answers
You can create a subquery for this process as follows.
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 tonull
as a result of no matching row for the sub-select. You can either supply an appropriatewhere
clause or you could use the result of that query as the leading column of thecoalesce()
function and supply the existingy
value as the training column. See documentation. Your query then becomes: (see demo here)