I have Table #1 (_Analytics) that has a field "IllnessBurden ...." which needs to have stored the # of records found in Table #2 (_Conditions). The two are linked by the patient ID. My UPDATE statement runs, but it is storing in each _Analytics record the total # of records found in the _Analytics table which is not what I want. I need to store the count of records that match the both tables. What am I doing wrong?
SET IllnessBurden_NumberChronic = (SELECT COUNT(*)
INNER JOIN ass_PCAS_Analytics
ON ass_PCAS_Conditions.TPA_Patient_ID = ass_PCAS_Analytics.TPA_Patient_ID
WHERE ass_PCAS_Conditions.CAMPAIGN_ID = 'RAP-2014')