UPDATE syntax for one to many - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

UPDATE syntax for one to many

Author

Topic

Ironwil
Starting Member

8 Posts

Posted - 2011-11-29 : 11:49:59

I need to write up an UPDATE statement that will grab the last comment for a particular project and assign that to a 'last-comment' field in the project table. I usually use an ORM for my data access, and am not SQL-savvy beyond basic statements. Basically, this is what I need to do:

table: Projects
table: ProjectComments

UPDATE Projects
SET Projects.LastComment =
(SELECT TOP (1) ProjectComments.Comment
WHERE Projects.ProjectID = ProjectComments.ProjectID
ORDER BY ProjectComments.Date DESC)

I know that's completely wrong, but it should illustrate what I'm trying to do. I need to grab all ProjectComment entries that have a ProjectID equal to the current Project row being updated, sort them by date descending, get the Comment field value from it, and use that value to update the Project.LastComment field. Any help would be greatly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-29 : 11:53:42

[code]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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ironwil
Starting Member

8 Posts

Posted - 2011-11-29 : 13:40:43

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.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-29 : 16:14:06

Maybe this will work:

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-30 : 01:32:09

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.

its supported in sql 2005. check if compatibility level used is 90 or above

EXEC sp_dbcmptlevel 'yourdb'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ironwil
Starting Member

8 Posts

Posted - 2011-11-30 : 12:36:34

My compatibility level is 90.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 00:12:06

quote:
Originally posted by Ironwil

My compatibility level is 90.

then APPLY should work


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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources