Solved

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

Posted on 2015-01-09
4
119 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 33

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 65

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 69

Accepted Solution

by:
ScottPletcher earned 500 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

758 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now