Update number of rows to a table

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

Other Forums

MS Access

Update number of rows to a table

Author

Topic

askQuest
Starting Member

3 Posts

Posted - 2010-04-23 : 08:25:14

I have two tables called Campus and Room:
Campus(campCode, numOfRm)
Room(rmNo, campCode)

I want to count the number of rooms of different campus in the table "Room" and then update the result to the table "Campus".

I used "Count" to calculate the number of rooms:
SELECT campCode, Count(rmNo) AS totRm FROM Room GROUP BY campCode;

So what should I do to update the result to "Campus"?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-23 : 08:39:34

Try this

update campus
set numOfRm=(SELECT Count(rmNo) AS totRm FROM Room Where campCode=campus.campCode)

Madhivanan

Failing to plan is Planning to fail

askQuest
Starting Member

3 Posts

Posted - 2010-04-23 : 10:56:40

quote:
Originally posted by madhivanan

Try this

update campus
set numOfRm=(SELECT Count(rmNo) AS totRm FROM Room Where campCode=campus.campCode)

Madhivanan

Failing to plan is Planning to fail

"Operation must use an updateable query" error was returned when running the code. I have tried to update the table with the help of INNER JOIN but still the same error was returned.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-23 : 12:12:07

It is not what you want to hear I know

But there is no need to store this value as you can always get it fresh and actual, while storing this value means it can be wrong after a new campus-room-relation is inserted.

No, you're never too old to Yak'n'Roll if you're too young to die.

askQuest
Starting Member

3 Posts

Posted - 2010-04-24 : 05:15:50

quote:
Originally posted by webfred

It is not what you want to hear I know
But there is no need to store this value as you can always get it fresh and actual, while storing this value means it can be wrong after a new campus-room-relation is inserted.

No, you're never too old to Yak'n'Roll if you're too young to die.

So is it possible to use a SQL query to update the value to the campus table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources