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

Time:04-09

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

sqlfiddle

  • Related