Consider this table:

Declare @Content table (id int, ParentId int, CreatedOn DateTime, LastCommentOn DateTime)

insert into @Content values
(1, null, GETDATE() - 10, '2001-12-01'),
(2, 1, GETDATE() - 9, GETDATE() - 8),
(3, 1, GETDATE() - 8, GETDATE() - 7),
(4, 1, GETDATE() - 7, GETDATE() - 6),
(5, null, GETDATE() - 6, '2001-12-01'),
(6, 5, GETDATE() - 5, GETDATE() - 4),
(7, 5, GETDATE() - 4, GETDATE() - 3),
(8, null, GETDATE() - 3, '2001-12-01'),
(9, 8, GETDATE() - 2, GETDATE() - 1)

I want to update all main content (identified by ParentId is null) to the CreatedOn date of the last comment on that content.

I've tried

update @Content m
set LastCommentOn = MAX(select CreatedOn from @Content c where c.ParentId = m.Id)
where ParentId is null and LastCommentOn = '2001-12-01'

update @Content
set LastCommentOn = MAX(select CreatedOn from @Content c where c.ParentId = m.Id)
from @Content m
where ParentId is null and LastCommentOn = '2001-12-01'

but I can't get it to do what I want..

How do I do this in MSSQL please?

(Also, it the query going to be the same on mysql?)

The accepted answer worked great on MS SQL, but, on MySql, I couldn't find a way to do it in one statement, I had to split the query into two parts and update.. so this is what worked for me on mysql

SET SQL_SAFE_UPDATES=0;

Create temporary table tmpContentDates 

select Max(ParentId) as pid, Max(CreatedOn) as pd
From Content
where ParentId is not null
Group By ParentId; 

update Content as c
    inner join tmpContentDates d on c.Id = d.pid
set c.LastCommentedOn = d.pd
where ParentId is null
    and LastCommentedOn = '2001-12-01';

drop table tmpContentDates;

Have you tried?

update @Content
set LastCommentOn =
(select MAX(CreatedOn) from @Content c where c.ParentId = m.Id)
from @Content m
where ParentId is null and LastCommentOn = '2001-12-01'