CODEWITHSUNDEEP
t-sqlsql-update
Pure.Krome
Pure.Krome

Reputation: 86957

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

A board post has zero to many comments.

I wish to set the ModifiedOn field to be the most recent comment date, if the board has a comment. Otherwise, just leave it null.

How do i do this, using TSql ?

something like ...

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

???

Upvotes: 0

Views: 95

Answers (2)

Dave Barker
Dave Barker

Reputation: 6437

I think this will work...

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

Upvotes: 2

Eric
Eric

Reputation: 95133

I decided to take into account both commented and uncommented posts:

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!

Upvotes: 0

Related Questions