T-SQL Update Column from other Tbl

I'm trying to update a column of one tbl with the sum of several columns of another joined tbl and receive the following error:

Msg 157, Level 15, State 1, Line 2
An aggregate may not appear in the set list of an UPDATE statement.

How would I accomplish this? Thanks!!
update e
	set Paid = (Sum(a.LossPaid-a.LossRecoveredSub-a.LossRecoveredSal-a.LossRecoveredOther)) from p
					where e..ClaimNumber = p.ClaimNumber)
	from SAP2007ClaimMaster e
		where exists (select null from p
						where e.ClaimNumber = p.ClaimNumber)

Open in new window

RickzzzAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
this should do:
update e
  set Paid = (select Sum(p.LossPaid-p.LossRecoveredSub-p.LossRecoveredSal-p.LossRecoveredOther)
                from p
               where e.ClaimNumber = p.ClaimNumber
             )
  from SAP2007ClaimMaster e
  where exists (select null from p
                  where e.ClaimNumber = p.ClaimNumber
                   )

Open in new window

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
i dont know what 'a.' stands for

you can modify the query in this way

Update E
SEt Paid = a.Paid
FROM  SAP2007ClaimMaster e
INNER JOIN (
 SELECT ClaimNumber, Sum(a.LossPaid-a.LossRecoveredSub-a.LossRecoveredSal-a.LossRecoveredOther)
 FROM p
 )P  on e.ClaimNumber = p.ClaimNumber
0
 
RickzzzAuthor Commented:
Not 'a.', should have been 'p.'...

There is something not working with your code, or how I'm using it anyway. Is that suppose to what I intended above, w/o the error? I may be goofing something.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can any of the values from "p" be null?

the suggestion with JOIN does not work, as you cannot SUM() in a UPDATE directly without subquery/inline view.
0
 
RickzzzAuthor Commented:
Perfect... thanks angelIII
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.