Solved

SQL update using sub query and MAX() function

Posted on 2009-07-06
15
540 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now