update table from job? - 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

SQL Server 2000 Forums

Transact-SQL (2000)

update table from job?

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-02-18 : 15:35:58

I have a table 'topstates' that I would like to populate daily from values of a select statement. I thought I would do this in a job. I can't seem to find out how to get the value from statements like the ones below inserted into the specific cell in the 'topstates' table

SELECT sum(votes) FROM tbluserdetails WHERE stateProvID='1'
SELECT sum(votes) FROM tbluserdetails WHERE stateProvID='2'
SELECT sum(votes) FROM tbluserdetails WHERE stateProvID='3'
SELECT sum(votes) FROM tbluserdetails WHERE stateProvID='4'

Any direction much appreciated! ..Thanks

Mike

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-02-18 : 15:46:42

Does this do what you want?

Insert Into topstates
(specific_cell_name)
SELECT sum(votes) FROM tbluserdetails WHERE stateProvID='1'

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-02-18 : 15:54:42

Insert Into topstates
(specific_cell_name)
SELECT sum(votes) FROM tbluserdetails WHERE stateProvID='1'

I believe so, except the the cell is determined by this statement

SELECT votes FROM tblTopStates WHERE stateProvID='1'

How would I do that

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-02-18 : 15:58:35

I'm not sure what you mean.

Can you give us an example of the select query result and what you would expect the topstates table to look like ?

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-02-18 : 16:08:38

This statement returns a value

SELECT sum(votes) FROM tbluserdetails WHERE stateProvID='1'

------------------------

I would like that value inserted into all cells that fit this criteria

SELECT votes FROM tblTopStates WHERE stateProvID='1'

Hope that clears it up.

Thanks

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-02-18 : 16:22:53

Still not quite sure what you mean, however, it sounds like you are wanting to update the votes field in topstates with sum(votes) matching on stateProvID ....Is that right ?

If so , you need something like :

Update topstates
Set votes = s.s_votes
From topstates as t
Join (select sum(votes) as s_votes, stateProvID from tbluserdetails) as s
On t.stateProvID = s.stateProvId

Haven't had a chance to test this so it may need some tweaking ...

ToddV
Posting Yak Master

218 Posts

Posted - 2002-02-18 : 16:24:24

Mike this will do all the states at once. I think this is what you want:


UPDATE A
SET Votes = B.Votes
FROM tblTopStates A
     JOIN (SELECT Sum(Votes)AS "Votes",stateProvID
           FROM tbluserdetails 
           GROUP BY  stateProvID) AS B
         ON A.stateProvID = B.stateProvID

ToddV
Posting Yak Master

218 Posts

Posted - 2002-02-18 : 16:25:41

Drats...Sniped again. You do need the group by in your derived table though.

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-02-18 : 16:26:56

"Group By" - knew I'd forgotten something !! Doh !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources