Too much egg nog - Query Help - lol 1

[JayKusch]

I am trying to update a record set with the last restore date that is captured with a TOP clause in the code.

Here is the code (that does not work):

UPDATE	MyTable
  SET MyDate = 
       ( SELECT TOP 1 RESTORE_DATE
         FROM MSDB.dbo.RESTOREHISTORY WITH (nolock)
	 JOIN	MyTable
	 ON	DESTINATION_DATABASE_NAME = DatabaseName )
FROM       MyTable	AS mt
INNER JOIN MSDB.dbo.RESTOREHISTORY                
ON	   DESTINATION_DATABASE_NAME = mt.DatabaseName

Thanks!

Thanks

J. Kusch

 

[gmmastros]

Try this:

UPDATE MyTable
SET    MyDate = MaxRestoreDates.Restore_Date
FROM   MyTable AS mt
       INNER JOIN (
          SELECT Max(restore_date) As Restore_Date, 
                 destination_database_name
          FROM   MSDB.dbo.RESTOREHISTORY WITH (nolock)
          Group By destination_database_name
          ) As MaxRestoreDates
              ON MaxRestoreDates.DESTINATION_DATABASE_NAME = mt.DatabaseName

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JayKusch]

PERFECT!

Thanks

J. Kusch