I want to use an aggregate function (Count) to set a field value in an Update
statement. I get an error that states this is not allowed. I could do the
aggregation in a view and then update the field value based on the view, but
is there a better? Following is what I want to do:

UPDATE PCP_Measures
SET Med_Claim_Memb_Cnt = COUNT(DISTINCT CLAIM_MASTERS.MEMBID)
FROM CLAIM_MASTERS
WHERE
CLAIM_MASTERS.PCP = PCP_Measures.PCP
AND CLAIM_MASTERS.LOB = PCP_Measures.LOB
GROUP BY
CLAIM_MASTERS.PCP,
CLAIM_MASTERS.LOB

Robert,

UPDATE PCP_Measures
SET Med_Claim_Memb_Cnt = (SELECT COUNT(DISTINCT CLAIM_MASTERS.MEMBID)
FROM CLAIM_MASTERS
WHERE
CLAIM_MASTERS.PCP =
PCP_Measures.PCP
AND CLAIM_MASTERS.LOB =
PCP_Measures.LOB
)



Ilya

"Robert Hamilton" <RobertHamilton (AT) discussions (DOT) microsoft.com> wrote in
message news:371AE6A1-BA7B-41A7-A93D-D726EE12E0DE (AT) microsoft (DOT) com...

Thanks!

"Ilya Margolin" wrote:

Hi Ilya,

I think you suggestion is in the right ballpark, but I still can't get it to
work properly. What happens when I use your suggestion is that all update
fields are updated to the result of the last combination of LOB/PCP. Any
ideas?

"Ilya Margolin" wrote: