Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 28th, 2007, 11:12 AM
Authorized User
 
Join Date: Jun 2006
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to jlrolin Send a message via MSN to jlrolin
Default UPDATE from COUNT(*) in another table.

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
__________________
________________________
ASP.NET 1.1/2.0 Developer
VB.NET Developer
 
Old March 20th, 2007, 01:57 AM
Authorized User
 
Join Date: Sep 2004
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Row Count in each table ayazhoda Access VBA 0 April 15th, 2008 04:31 PM
Update Query to add a count of +1 melkin Classic ASP Databases 3 August 7th, 2007 11:59 AM
Getting item count from another table jdebelen SQL Language 1 February 3rd, 2005 05:27 AM
count the number of rows in the table crmpicco Javascript How-To 4 February 2nd, 2005 12:58 AM
Update parent table with the sum of child table gbrown SQL Language 2 November 9th, 2004 07:53 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.