How to update a table from a query result of another table related to the first one? 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;
I think what you are searching for is


CREATE TABLE table_a (&quot;id&quot; int , &quot;firstCreationTime&quot; timestamp)




INSERT INTO table_a VALUES (1,CURRENT_TIMESTAMP - INTERVAL '1 days')




CREATE TABLE table_b (&quot;id&quot; int , &quot;created_at&quot; 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 &quot;id&quot;, MIN(&quot;created_at&quot;) as &quot;created_at&quot; FROM table_b GROUP BY &quot;id&quot;)
UPDATE table_a AS a 
SET &quot;firstCreationTime&quot; = &quot;created_at&quot; 
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&lt;&gt;fiddle here