SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 updating a column from the MAX of the value
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

somebodynew
Starting Member

2 Posts

Posted - 03/07/2013 :  09:11:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/07/2013 :  09:47:56  Show Profile  Reply with Quote
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 - 03/07/2013 :  10:39:39  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 03/07/2013 :  10:59:15  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000