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
CodePudding user response:
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;
CodePudding user response:
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