Have Geek Brain

How do I update this field in my sql database?

I have two tables.

BoardPosts
BoardPostId INT PK
ModifiedOn DATETIME NULLABLE

BoardComments
BoardCommentId INT PK
BoardPostId INT
CreatedOn DATETIME

The forum post has zero comments.

I want to set the field ModifiedOn

as the most recent comment date if there is a comment on the board. Otherwise, just leave it at zero.

How can I do this using TSql?

sort of...

UPDATE BoardPosts
SET ModifiedOn = CreatedOn
SELECT TOP(1) CreatedOn
FROM BoardPosts a INNER JOIN BoardComments b ON a.BoardPostId = b.BoardPostId

???

0


a source to share


2 answers


I think it will work ...



UPDATE BoardPosts
SET ModifiedOn = (SELECT MAX(CreatedOn) 
                  FROM BoardComments 
                  WHERE BoardComments.BoardPostId = BoardPosts.BoardPostId)
+2


a source


I decided to take into account both comments and no comments:

update p1 set ModifiedOn = (
select
    max(case when c.CreatedOn is null then p.CreatedOn
    else c.CreatedOn end) as ModDate
from 
    boardposts p
    left outer join BoardComments c on
        p.BoardPostId = c.BoardPostId
where
    p.BoardPostId = p1.BoardPostId
group by p.BoardPostId
)
from
    BoardPosts p1


Hope this helps!

0


a source






More articles:


All Articles