How do I update 1 table with summary values from another table?

Question

How do I update 1 table with summary values from another table?

Viewing 6 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

First, having Activity1Count, Activity2Count, etc., is a violation of normal form basics.

Second, I wouldn't store those in a table. I'd calculate them at runtime in a query of the table.

Third, if you absolutely must store them in that table (I recommend strongly against it), are you familiar with correlated inline sub-queries?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

jeff.cilcus Grasshopper Points: 18 More actions · Answer 2

Hi GS,

I am storing the Activity values in the staff table for a specific month. Then, once the values are verified they get posted to another Year to Date column. I need the ability to edit those values to account for some things that aren't captured in the activities table.

I don't know what you mean by correlated inline sub-queries, but I bet that's what I'm looking for.

Thanks!

GSquared SSC Guru Points: 260824 More actions · Answer 3

update dbo.Staff

set Col1 = (select count(*) from dbo.Activities where StaffID = Staff.StaffID);

Something like that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

jeff.cilcus Grasshopper Points: 18 More actions · Answer 4

Hi GS!

That's perfect!! Works exactly like I need it to. Thank you so much for your help!

take care,

GSquared SSC Guru Points: 260824 More actions · Answer 5

You're welcome.

I still recommend moving away from this data model. Can't recommend a specific solution without more details about your structure, but there's got to be something better.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

jeff.cilcus Grasshopper Points: 18 More actions · Answer 6

Hi GS,

I'm sure you're right and at some point we'll probably chat about this, but at least for now this will work for me. Thanks again and talk soon.

Jeff