sql - update statement using nested query

Question

I have a table:

mytable:
    id
    userID
    logDate
    lastLogDate

For every row in that table, I want to update the 'lastLogDate' column to be the max value of logDate on a per user basis...

Conceptually, each user should have a lastLogDate = the value that is returned by:

select max(logDate) from mytable group by userID

Can somebody help me write the update statement for that?

Answer 1

Something like this?

UPDATE mytable SET lastLogDate = t.maxDateForUser  
FROM  
(  
    SELECT userid, MAX(logDate) as maxDateForUser  
    FROM mytable  
    GROUP BY userId  
) t  
WHERE mytable.userid = t.userid