We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

SQL update using sub query and MAX() function

Medium Priority
602 Views
Last Modified: 2012-06-27
I have two tables, ChangeDetails and History.  History has multiple history items for each item in the ChangeDetails table.  I have created a new field in the Change Details table called 'Last_Updated'.  I want to fill this with the date of the most recent history item for each.

ChangeDetails has the following fields:
-ChangeID (primary key)
....(a few others)
-Last_Updated
-Last_Updated_By

History has the following fields
-NoteID (primary key)
....(a few others)
-NoteTime (datetime field)

Please advise the SQL query to fill the Last_Updated field within the ChangeDetails table using the most recent NoteTime for that ChangeID.
Comment
Watch Question

Commented:
UPDATE ChangeDetails
SET Last_Updated = Date
From ChangeDetails c INNER JOIN (SELECT NoteID, MAX(NoteTime) Date From History Group by NoteID) a
           ON c.ChangeID = a.NoteID
Assumming the 2 tables link on ChangeID and NoteID

Author

Commented:
Hi pssandhu, thanks for your prompt reply.
The tables link on ChangeID for each table.  NoteID is the unique key for each history item.

Commented:
Okay, so I guess there is a ChangeID in history table too?
UPDATE ChangeDetails
SET Last_Updated = Date
From ChangeDetails c INNER JOIN (SELECT ChangeID, MAX(NoteTime) Date From History Group by NoteID) a
          ON c.ChangeID = a.ChangeID

Commented:
Sorry correction, forgot to update the group by clause in the subquery
UPDATE ChangeDetails
SET Last_Updated = Date
From ChangeDetails c INNER JOIN (SELECT ChangeID, MAX(NoteTime) Date From History Group by ChangeID) a
         ON c.ChangeID = a.ChangeID  

Author

Commented:
pssandhu,
Yes there is a ChangeID in the history table too, sorry, I forgot to put that in! :)

This query works perfectly, thank you.  I'd like to make one addition though.  I've now added another field in ChangeDetails called Last_Updated_By.  History already has the field 'User'.  How can this query be modified to update Last_Updated_By with the username in history.user?

Thanks!

Commented:
No problem. Please try this out:

UPDATE ChangeDetails
SET Last_Updated = Date,
    Last_Updated_by = us
From ChangeDetails c INNER JOIN (
							Select h.ChangeID, h.NoteTime as Date, h.User as us
							From History h inner join (SELECT ChangeID, MAX(NoteTime) Date From History Group by ChangeID) j
								 ON h.ChangeID = j.ChangeID and h.NoteTime = j.Date ) a
        ON c.ChangeID = a.ChangeID  

Open in new window

Author

Commented:
pssandhu, I get the following error when checking the sytax of this command:

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'User'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'j'.

Any idea what is happening?

Commented:
Not sure - can you run this subquery by itslef to see if you get the same error or if it runs fine:
 

Select  h.ChangeID, h.NoteTime as Date, h.User as us
From    History h inner join (SELECT ChangeID, MAX(NoteTime) Date From History Group by ChangeID) j
        ON h.ChangeID = j.ChangeID and h.NoteTime = j.Date

Open in new window

Author

Commented:
Still getting problem.  Here is the error I get now:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'User'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'j'.

Would it be easier to update the Last_Updated_By using a seperate query?  These queries are only a one-off to prefil existing records.

Thanks.

Commented:
Sorry but I am totally baffled. The syntax looks absolutely fine. Creating 2 seperate queries won't help because for Last_Updated_by still have to use the subquery we are trying to troubleshoot. Let's give this a try, just the subquery for now:
 

Select  [History].[ChangeID],
		[History].[NoteTime], 
		[History].[User]
From    [History] INNER JOIN (	Select	[History].[ChangeID],
										MAX([History].[NoteTime]) as Date
							    From	[History] 
								Group by [History].[ChangeID]
						     ) as Hist
        ON [History].[ChangeID] = [Hist].[ChangeID] and [History].[NoteTime] = [Hist].[Date]

Open in new window

Author

Commented:
Great, this works.

Here is a sample of the results:
ChangeID,NoteTime,User
538, 2009-06-30 12:26:37.000, yipci
537, 2009-06-29 08:10:43.000, hetherg
535, 2009-05-22 14:17:33.000, nelsoj
530, 2009-05-22 11:44:27.000, rajasinm
528, 2009-05-21 18:02:44.000, fitzgerl
526, 2009-05-21 13:50:48.000, ellwoodg
525, 2009-05-21 10:01:38.000, eastwood
524, 2009-05-20 19:25:00.000, gomezb
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thats done it!  Thank you very much for you help!

Author

Commented:
Perfect, many thanks!

Commented:
No problem. Glad I could help.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.