Solved

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

Posted on 2015-01-09
4
132 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 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:
Scott Pletcher 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

751 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