Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 updating a column from the MAX of the value

Author  Topic 

somebodynew
Starting Member

2 Posts

Posted - 2013-03-07 : 09:11:52
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!

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-07 : 09:47:56
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
Go to Top of Page

somebodynew
Starting Member

2 Posts

Posted - 2013-03-07 : 10:39:39
That was the first query I tried. I get an error o

ERROR: Incorrect syntax near the keyword 'Group'.
Error Code:
156

A search of that error code yielded no fruit. I have to assume I have written the query wrong. Thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-07 : 10:59:15
quote:
Originally posted by MIK_2008

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/

Go to Top of Page
   

- Advertisement -