Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 558
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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