Re: SQL?: Group By Within UPDATE How??

[Rick_Creel@AONCONS.COM]

If I were doing this, here is what I would do:

begin-procedure count_stuff
begin-select t1.type, count(*)
   let #ctr = count(*)
   do update_table2
from table1 t1
group by t1.type
end-select
end-procecure

begin-procedure update_table2
begin-sql
update table2 t2
set t2.countofexamples = #ctr
where t2.type = &t1.type
end-sql
end-procedure






NEDOLPH@AOL.COM on 05/10/2000 06:50:48 AM

Please respond to sqr-users@list.iex.net

To:   SQR-USERS@list.iex.net
cc:    (bcc: Rick Creel/IT/Aon Consulting)

Subject:  SQL?: Group By Within UPDATE How??



This is really more of an SQL problem than an SQR problem, but I hope
somebody knows the answer.

The problem is Combining a GROUP BY Clause with or within an UPDATE Query.
I want to UPDATE the rows in table 2 with the Counts from table 1.

Suppose 2 tables:

Table 1 "Examples"
    (Could be uniquely indexed across both columns if it mattered.)

Column1 Column2
Type:       Example:
-------  -------
Animal  Horse
Animal  Dog
Animal  Monkey
Vegie       Cabbage
Mineral Rock
Mineral     HardPlace

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

Table 2     "Counts"

Column1 Column2
Type:       CountOfExamples:
-------  ----------------
Animal  0 initially, after the query    should be 3
Vegie       0 initially, after the query    should be 1
Mineral     0 initially, after the query    should be 2

I can do a GroupBy(Examples.Type) query against table1
to generate the values 3,1,2
but how do I get them posted into Table 2?

Any solution would be welcomed, one step, two or even something that
generates an interim temp table.

Warmest thanks,

Norman Dolph