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

UPDATE from COUNT(*) of another table in DB2 - HOW?

P: 3
I'm trying to update a new field in a table from a COUNT(*) of Registration IDs grouped by Course IDs.

COUNT: Course_ID
11 1234
12 2323
19 8932

I want to populate a NUM_REG field in another table with the COUNT from this query to the records that have matching Course_IDs.

Similar to this situation:
Expand|Select|Wrap|Line Numbers
  1. update a 
  2. set a.cnt = b.cnt 
  3. from table2 a, 
  4.         (select widget_type, count(*) as cnt 
  5.          from table1 
  6.          group by widget_type) b 
  7. where a.widget_type = b.widget_type 
  8.  
Only problem is, that code will not work in DB2, keeps giving me an error about unexpected token after the beginning of the statement.

Any suggestions?
Feb 28 '07 #1
Share this Question
Share on Google+
1 Reply


P: 24
I do not have Db2 running on my server to try this SQL but you should be able to do it with something like:

update table2 as a
set a.cnt = (Select count(*)
from table1 as b
where a.widget_type = b.widget_type)

Regards

Snib
Mar 17 '07 #2

Post your reply

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