How to update a table from a query result of another table related to the first one?

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.

Method 1

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_a

id | firstCreationTime         
-: | :-------------------------
 1 | 2021-03-11 17:13:08.966639

db<>fiddle here

Method 2

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 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0