• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 142
  • Last Modified:

How to use SELECT COUNT to update total record count in second table

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?

UPDATE ass_PCAS_Analytics
SET IllnessBurden_NumberChronic = (SELECT COUNT(*)
                                                      FROM ass_PCAS_Conditions
                                                      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')
0
silverwind
Asked:
silverwind
1 Solution
 
Mike EghtebasDatabase and Application DeveloperCommented:
UPDATE ass_PCAS_Analytics p
SET IllnessBurden_NumberChronic = (SELECT COUNT(*)
                                                      FROM ass_PCAS_Conditions pc
                                                      INNER JOIN p
                                                      ON pc.TPA_Patient_ID = p.TPA_Patient_ID)
                                                      WHERE pc.CAMPAIGN_ID = 'RAP-2014'
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
> but it is storing in each _Analytics record the total # of records found in the _Analytics table which is not what I want.
For starters, when you have more than one table in a SELECT clause and use the *, the * will return all rows in the return set, and not just the table in the FROM clause.   So it's likely that COUNT(*) was returning the row count from BOTH tables in that query's return set.  So, give this a whirl (replace id with the PK of ass_PCAS_Conditions)..
UPDATE ass_PCAS_Analytics 
SET IllnessBurden_NumberChronic = (
	SELECT COUNT(c.id) 
	FROM ass_PCAS_Conditions c
		JOIN ass_PCAS_Analytics a ON c.TPA_Patient_ID = a.TPA_Patient_ID 
	WHERE a.CAMPAIGN_ID = 'RAP-2014')

Open in new window

0
 
Scott PletcherSenior DBACommented:
You have to insure that SQL matches the patient ids as you want it to for the count.  In this case, that means that the UPDATE table must be directly correlated to the SELECT, like this:


UPDATE apa
SET IllnessBurden_NumberChronic = (SELECT COUNT(*)
                                       FROM ass_PCAS_Conditions apc
                                       WHERE
                                           apc.TPA_Patient_ID = apa.TPA_Patient_ID  AND
                                           apc.CAMPAIGN_ID = 'RAP-2014')
FROM ass_PCAS_Analytics apa
0
 
silverwindAuthor Commented:
Thanks, yours was the one I tried and it worked as I needed it to.  It was the WHERE clause that was the problem.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now