By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,654 Members | 1,513 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,654 IT Pros & Developers. It's quick & easy.

Newbie : Updating a table from a query

P: 5

I have two tables

Table1 has three fields empid,elid, stats
Empid and Elid are unique
Table2 contains many values of empid,elid and apstatid

I need to do something like this
UPDATE Table1 SET Table1.stats= (SUM(Table2.apstatid)/COUNT(Table2.apstatid)) FROM
Table2 WHERE Table2.empid= Table1.empid AND Table2.elid= Table1.elid

How do I do it?

Mar 1 '08 #1
Share this Question
Share on Google+
2 Replies

Expert 100+
P: 1,134
You nearly had it!

Expand|Select|Wrap|Line Numbers
  1. UPDATE Table1 
  2. SET Table1.stats = 
  3. (   SELECT SUM(Table2.apstatid)/COUNT(Table2.apstatid)
  4.     FROM Table2 
  5.     WHERE Table2.empid= Table1.empid 
  6.          AND Table2.elid= Table1.elid 
  7. )
Mar 2 '08 #2

P: 5

That did it.

With a little help from Query Optimizer the 3.5hr operation not takes 3 secs :-)

Mar 2 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.