UPDATE p
SET p.LastComment = p1.Comment 
FROM Projects p
CROSS APPLY 
(
SELECT TOP (1) Comment 
FROM ProjectComments
WHERE p.ProjectID = ProjectID
ORDER BY Date DESC
)p1

UPDATE 
    P
SET 
    LastComment = Comment
FROM
    Projects AS P    
INNER JOIN
    (
        SELECT 
            ProjectID, 
            Comment,
            ROW_NUMBER() OVER (PARTITION BY ProjectID ORDER BY [Date] DESC) AS RowNum
        FROM 
            ProjectComments
        
    ) AS PC
    ON P.ProjectID = PC.ProjectID
WHERE
    PC.RowNum = 1        

quote:

---

Originally posted by Ironwil

Thanks. I tried your solution, but received the error message that cross apply was not supported. It might be a version issue. I'm using SQL Server 2005. I see that I posted this question in the 2008 forum, so I apologize for the mistake.

---

quote:

---

Originally posted by Ironwil

My compatibility level is 90.

---

UPDATE p
SET p.LastComment = p1.Comment 
FROM Projects p
CROSS APPLY 
(
SELECT TOP (1) Comment 
FROM ProjectComments
WHERE ProjectID = p.ProjectID
ORDER BY Date DESC
)p1