kpwhitte
asked on
Error trying to sum values in an update statment
Now i need to sum values from two different CostCodeTypes. Following is my code
update jc
set Equipment = sum (cc.Committed)
from JobCostCommitted jc
inner join qry_Const_JobDetail_Commit
on cc.JobNumber = jc.JobNumber
where cc.CostCodeType in ('E','T')
here is the error I get
Server: Msg 157, Level 15, State 1, Line 2
An aggregate may not appear in the set list of an UPDATE statement.
update jc
set Equipment = sum (cc.Committed)
from JobCostCommitted jc
inner join qry_Const_JobDetail_Commit
on cc.JobNumber = jc.JobNumber
where cc.CostCodeType in ('E','T')
here is the error I get
Server: Msg 157, Level 15, State 1, Line 2
An aggregate may not appear in the set list of an UPDATE statement.
ASKER
Server: Msg 8118, Level 16, State 1, Line 1
Column 'jc.JobNumber' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Column 'jc.JobNumber' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
set Equipment = tot
FROM JobCostCommitted jc
INNER JOIN (
SELECT sum (cc.Committed) tot, jc.JobNumber
from JobCostCommitted jc
inner join qry_Const_JobDetail_Commit
on cc.JobNumber = jc.JobNumber
where cc.CostCodeType in ('E','T') ) A
On jc.JobNumber = A.JobNumber