All we need is an easy explanation of the problem, so here it is.
Here is my attempt. What is the correct syntax to achieve this?
UPDATE table_a a SET a.firstCreationTime = c.created_at FROM ( SELECT created_at FROM table_b b WHERE a.id = b.id ORDER BY b.created_at ASC LIMIT 1 ) AS c;
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
I think what you are searching for is
CREATE TABLE table_a ("id" int , "firstCreationTime" timestamp)
INSERT INTO table_a VALUES (1,CURRENT_TIMESTAMP - INTERVAL '1 days')
CREATE TABLE table_b ("id" int , "created_at" timestamp)
INSERT INTO table_b VALUES (1,CURRENT_TIMESTAMP - INTERVAL '10 days') ,(1,CURRENT_TIMESTAMP - INTERVAL '20 days'),(1,CURRENT_TIMESTAMP - INTERVAL '30 days')
WITH desired AS( SELECT "id", MIN("created_at") as "created_at" FROM table_b GROUP BY "id") UPDATE table_a AS a SET "firstCreationTime" = "created_at" FROM desired b where a.id = b.id
1 rows affected
SELECT * FROM table_aid | firstCreationTime -: | :------------------------- 1 | 2021-03-11 17:13:08.966639
Something like this might do the trick (untested as I don’t have any table structures or sample data!).
UPDATE table_a a SET a.firstCreationTime = c.f_ca FROM ( SELECT b_id, f_ca FROM ( SELECT b.id AS b_id, b.created_at AS f_ca, ROW_NUMBER() OVER (PARTITION BY b.id ORDER BY b.id, b.created_at ASC) AS rn FROM table_b b WHERE a.id = b.id ) AS x WHERE x.rn = 1 ) AS c WHERE a.id = c.b_id;
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂