Update table A's column with oldest value from joined column B

Question

I have :

post(id, date)
comment(id, post_id, date, checked)

I'd like to update posts that :

• have a null date column
• have a comment with checked as true

with the date of the oldest comment having checked as true, without using a subquery for performance reason, assuming I'm using a large data set.

So far I have

update post p
set date = c.date
from post p2
         join comment c on p2.id = c.id and c.checked is true
where p.date is null

but it seems to update the date with the latest entry, and the ones that don't have a comment with checked as true also get affected

POST

id	date
1	null
2	null
3	null

COMMENT

id	post_id	date	checked
1	1	2020-01-01	true
2	1	2020-05-01	true
3	2	2020-03-01	false

POST AFTER UPDATE

id	date
1	2020-01-01
2	null
3	null

Posts 2 and 3 don't get updated because they don't have any comments with checked as true

Answer 1

You can try to UPDATE .... FROM subquery by using ROW_NUMBER window function to get oldest DATE from comment table.

UPDATE post p
SET date = c.date
FROM (
   SELECT *,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATE) rn
   FROM comment
) c 
where p.date is null AND 
rn = 1 AND
p.id = c.post_id AND
c.checked = true

sqlfiddle

Answer 2

I think that the problem is that you have put c.checked is true in the JOIN clause when it should be in the WHERE clause.
Please check the following update query:

update post p
set date = c.date
from post p2
join comment c on p2.id = c.id 
where p.date is null
and c.checked is true;