UPDATE from COUNT(*) in another table.

jlrolin · February 28th, 2007, 11:12 AM

I'm trying to run a script that will update an integer field in one table from a count in another table.

Right now, I have:

Code:

SELECT COUNT(*) AS numreg FROM REGISTRATION
    WHERE REGISTRATION.COURSE_OFFERING_ID =  
          COURSE_OFFERING.COURSE_OFFERING_ID
          AND REGISTRATION.DELETED_IND = 'N'

This grabs the count of each REGID associated with a CO_ID.
List is something like:

14 4334242
11 4324324
12 4325223
ETC.

I have a blank field in another table called NUM_REG.
I want to just insert the fields from the COUNT(*) into NUM_REG.

Any suggestions?

________________________
ASP.NET 1.1 Developer
VB.NET Developer

GeertVerhoeven · March 20th, 2007, 01:57 AM

Hi,

You can do the following:
UPDATE targetTable
SET NUM_REG = numreg
FROM targetTable
INNER JOIN (
SELECT CO_ID, COUNT(*) AS numreg FROM REGISTRATION
    WHERE REGISTRATION.COURSE_OFFERING_ID =
          COURSE_OFFERING.COURSE_OFFERING_ID
          AND REGISTRATION.DELETED_IND = 'N'
   GROUP BY CO_ID
) sourceTable ON targetTable.CO_ID = sourceTable.CO_ID

http://geertverhoeven.blogspot.com