Solved

SQL update using sub query and MAX() function

Posted on 2009-07-06
15
552 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

729 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