UPDATE Statement dilema

[JayKusch]

I have a table (TableA) that needs to have a date field updated with a MAX(Date) from another table (TableC).

To add to this ... We need to join to a TableB to get an ID to have it hook up right.

So TableA looks like:

DBName varchar(100),
CreateDate DateTime

TableB looks like:

DBName varchar(100)
DBID Int

TableC looks like:

DBID Int
DateCreated DateTime

So I have attempted something like this (but of course it does not work) ... What am I missing.

UPDATE	TableA

  SET  DateCreated = ( SELECT MAX(c.DateCreated)
                       FROM TableA  AS a
                       JOIN TableB  AS b
                       ON  (a.DBName = b.DBName)
                       JOIN TableC  AS c
                       ON  (b.DBID = c.DBID) )

Hope this makes sense.

Thanks ALL

Thanks

J. Kusch

 

[gmmastros]

try this:

Update TableA
Set    TableA.CreateDate = AliasName.MaxDateCreated
From   TableA
       Inner Join (
         Select TableB.DBName, Max(TableC.DateCreated) As MaxDateCreated
         From   TableB
                Inner Join TableC
                  On TableB.DBID = TableC.DBID
         Group By TableB.DBName
         ) As AliasName
         On TableA.DBName = AliasName.DBName

Since this is an update statement, you should probably make sure you have a good backup before running this. If this works and you want me to explain, let me know.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ousoonerjoe]

Another, not so fancy way to do it:

DECLARE @MaxDate	DATETIME;
SET @MaxDate = ( SELECT MAX(c.DateCreated)
                 FROM TableA  AS a
                       JOIN TableB  AS b ON  (a.DBName = b.DBName)
                       JOIN TableC  AS c ON  (b.DBID = c.DBID);

UPDATE TableA
SET  DateCreated =  @MaxDate

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[markros]

See thread183-1592420

PluralSight Learning Library