SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 UPDATE syntax for one to many
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ironwil
Starting Member

8 Posts

Posted - 11/29/2011 :  11:49:59  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 11/29/2011 :  11:53:42  Show Profile  Reply with Quote
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


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

Go to Top of Page

Ironwil
Starting Member

8 Posts

Posted - 11/29/2011 :  13:40:43  Show Profile  Reply with Quote
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.

Edited by - Ironwil on 11/29/2011 13:41:20
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 11/29/2011 :  16:14:06  Show Profile  Reply with Quote
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        

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/30/2011 :  01:32:09  Show Profile  Reply with Quote
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/

Go to Top of Page

Ironwil
Starting Member

8 Posts

Posted - 11/30/2011 :  12:36:34  Show Profile  Reply with Quote
My compatibility level is 90.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/01/2011 :  00:12:06  Show Profile  Reply with Quote
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/


Edited by - visakh16 on 12/01/2011 00:12:24
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000