• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 559
  • Last Modified:

SQL update using sub query and MAX() function

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.
0
GENINZ
Asked:
GENINZ
  • 8
  • 7
1 Solution
 
pssandhuCommented:
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
0
 
GENINZAuthor 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.
0
 
pssandhuCommented:
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
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
pssandhuCommented:
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  
0
 
GENINZAuthor 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!
0
 
pssandhuCommented:
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

0
 
GENINZAuthor 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?
0
 
pssandhuCommented:
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

0
 
GENINZAuthor 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.
0
 
pssandhuCommented:
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

0
 
GENINZAuthor 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
0
 
pssandhuCommented:
Woah, that's a reliefe - Okay, so let's use this subquery in your original update statement. Should look something like this:

UPDATE	[ChangeDetails]
SET		[ChangeDetails].[Last_Updated]		= [Summary].[NoteTime],
		[ChangeDetails].[Last_Updated_by]	= [Summary].[User]
From	[ChangeDetails] INNER JOIN (
									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]
								) as Summary
        ON [ChangeDetails].[ChangeID] = [Summary].[ChangeID]

Open in new window

0
 
GENINZAuthor Commented:
Thats done it!  Thank you very much for you help!
0
 
GENINZAuthor Commented:
Perfect, many thanks!
0
 
pssandhuCommented:
No problem. Glad I could help.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now