I have a master/detail relationship between tables. I need a query to update a numeric column in the master table with the max of a value from the detail where the PK of the master is a specified values.
This select statement gives me eyes into what I am looking at:
Select TestTempGeneralInfo.IssueID, MAX(TestTaskGeneralInfo.EffectiveTime) From TestTaskGeneralInfo INNER JOIN TestTempGeneralInfo ON TestTaskGeneralInfo.TestTemplateID = TestTempGeneralInfo.IssueID Where TestTempGeneralInfo.ProjectID = 150 Group By TestTempGeneralInfo.IssueID
The table with the word TEMP in it is the master. The table wiht TASK in it is the detail.
Here I am viewing the Issue ID and the MAX of the TASK EffectiveTime. The Issue ID is what joins to the TEMP (master) Table on TestTemplateID as you can see.
I have seen examples in the internet of an update with a max which are similar but do not include the where specification. Still and all I find myself confused on how the join between tables works.
Any help with this query would be much appreciated for the noob. Thanks!
UPDATE TestTempGeneralInfo SET TestTempGeneralInfo.ColumnName=MAX(TestTaskGeneralInfo.EffectiveTime) From TestTaskGeneralInfo INNER JOIN TestTempGeneralInfo ON TestTaskGeneralInfo.TestTemplateID = TestTempGeneralInfo.IssueID Where TestTempGeneralInfo.ProjectID = 150 Group By TestTempGeneralInfo.IssueID
Make sure that the destination(master) column datatype is same to that the detail column.
UPDATE TestTempGeneralInfo SET TestTempGeneralInfo.ColumnName=MAX(TestTaskGeneralInfo.EffectiveTime) From TestTaskGeneralInfo INNER JOIN TestTempGeneralInfo ON TestTaskGeneralInfo.TestTemplateID = TestTempGeneralInfo.IssueID Where TestTempGeneralInfo.ProjectID = 150 Group By TestTempGeneralInfo.IssueID
Make sure that the destination(master) column datatype is same to that the detail column.
Cheers MIK
wont work like this
it should be
UPDATE g
SET g.ColumnName= t.EffectiveTime
FROM TestTempGeneralInfo g
INNER JOIN (SELECT TestTemplateID,
MAX(EffectiveTime) AS EffectiveTime
From TestTaskGeneralInfo
GROUP BY TestTemplateID)t
ON t.TestTemplateID = g.IssueID
Where g.ProjectID = 150
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
updating a column from the MAX of the value
Posted - 03/07/2013 : 09:11:52
