I am creating a postgresql script that updates one product table based on another. the first table, we'll call T1 contains the product code and some descriptive info.The product code is the key for this table:

The 2nd table contains products, along with dates that show the availability of these products.

I then used ALTER TABLE to add begin and end_date columns to the 1st table. I then used an update statement:

UPDATE T1
SET
begin_date =  b.begin_date,
end_date = b.end_date
FROM T2 b JOIN T1 a ON a.product_code = b.product_code;

This gives does not give the correct result. For me, it seems that a single pair of values was populated rather than the correct values from T2.

I really have no idea what could be causing this. I know that the values being inserted are coming from somewhere in T2. However, I cant see any logical reason that all of these values should be the same.

CodePudding user response：

You don't need to join table T1 in your FROM clause. You can try this :

UPDATE T1 AS a
SET
begin_date =  b.begin_date,
end_date = b.end_date
FROM T2 AS b
WHERE b.product_code = a.product_code;