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.
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.IssueIDWhere TestTempGeneralInfo.ProjectID = 150Group By TestTempGeneralInfo.IssueIDThe 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.IssueIDWhere TestTempGeneralInfo.ProjectID = 150Group By TestTempGeneralInfo.IssueIDMake sure that the destination(master) column datatype is same to that the detail column.CheersMIK |
|
|
somebodynew
Starting Member
2 Posts |
Posted - 2013-03-07 : 10:39:39
|
That was the first query I tried. I get an error oERROR: Incorrect syntax near the keyword 'Group'.Error Code:156A search of that error code yielded no fruit. I have to assume I have written the query wrong. Thanks! |
|
|
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.IssueIDWhere TestTempGeneralInfo.ProjectID = 150Group By TestTempGeneralInfo.IssueIDMake sure that the destination(master) column datatype is same to that the detail column.CheersMIK
wont work like thisit should beUPDATE g SET g.ColumnName= t.EffectiveTimeFROM TestTempGeneralInfo gINNER JOIN (SELECT TestTemplateID,MAX(EffectiveTime) AS EffectiveTimeFrom TestTaskGeneralInfo GROUP BY TestTemplateID)t ON t.TestTemplateID = g.IssueIDWhere g.ProjectID = 150 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|