update data using group by - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

update data using group by

Author

Topic

venkatch786
Starting Member

8 Posts

Posted - 2010-10-07 : 08:45:58

I have a table having columns district_id and school_id tot_schools.
I have data for district_id and school_id.
for tot_schools NULL
now i want to update tot_schools columns with no.of schools in wach district

I get tha data like this way but I cant update for tot_schools column

SELECT DISTRICT_ID ,COUNT(SCHOOL_ID) FROM TABLE1
GROUP BY DISTRICT_ID

Thanks in advance...
venkateswarlu.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-07 : 14:10:02

Try this:

Update Table1 Set tot_schools = ST.RecCount
From Table1 T inner join
(
SELECT DISTRICT_ID ,COUNT(SCHOOL_ID) RecCount FROM TABLE1
GROUP BY DISTRICT_ID
) as ST
On T.DISTRICT_ID = ST.DISTRICT_ID

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources