Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2015-01-09
4
Medium Priority
?
139 Views
Last Modified: 2015-01-09
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
Comment
Question by:silverwind
4 Comments
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40541074
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40541116
> 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
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 40541206
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
 

Author Closing Comment

by:silverwind
ID: 40541305
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question