Solved

SQL update using sub query and MAX() function

Posted on 2009-07-06
15
542 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.
0
Comment
Question by:GENINZ
  • 8
  • 7
15 Comments
 
LVL 17

Expert Comment

by:pssandhu
ID: 24789380
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
 

Author Comment

by:GENINZ
ID: 24789414
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
 
LVL 17

Expert Comment

by:pssandhu
ID: 24789423
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 17

Expert Comment

by:pssandhu
ID: 24789429
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
 

Author Comment

by:GENINZ
ID: 24789723
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
 
LVL 17

Expert Comment

by:pssandhu
ID: 24789769
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
 

Author Comment

by:GENINZ
ID: 24790114
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
 
LVL 17

Expert Comment

by:pssandhu
ID: 24790151
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
 

Author Comment

by:GENINZ
ID: 24790203
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
 
LVL 17

Expert Comment

by:pssandhu
ID: 24790269
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
 

Author Comment

by:GENINZ
ID: 24790439
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
 
LVL 17

Accepted Solution

by:
pssandhu earned 125 total points
ID: 24790470
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
 

Author Comment

by:GENINZ
ID: 24790534
Thats done it!  Thank you very much for you help!
0
 

Author Closing Comment

by:GENINZ
ID: 31600363
Perfect, many thanks!
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24790540
No problem. Glad I could help.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Union 2 queries to a cte (temp table perhaps) 9 34
SQL Server 2012 r2 - Sum totals 2 23
SYbase 4 29
transaction in asp.net, sql server 6 31
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question