Solved

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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.

911 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

20 Experts available now in Live!

Get 1:1 Help Now