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

["Chang, Chaw-Ye" <CCHANG@WCUPA.EDU>]

If you only want to update the table2 rather than recreating it, on top of
my head you can do this in 2 steps :
1. create view table3 as select type, count(examples) cnt from table1 group
by type;
2. UPDATE table2 SET table2.count=(SELECT table3.CNT FROM table3 WHERE
table2.type=table3.type);

If you can recreate table2 each time (which will eliminate the case that a
new type is in table1 but not in table2), then :
drop table2;
create  table2 as select type, count(examples) cnt from table1 group by
type;

> -----Original Message-----
> From: NEDOLPH@AOL.COM [SMTP:NEDOLPH@AOL.COM]
> Sent: Wednesday, May 10, 2000 6:51 AM
> To:   SQR-USERS@list.iex.net
> 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