Wei Weng wrote:
> I made a mistake in the queries:
>
> They should be
>
> update T set t1 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da =
> 1) as b where tkey = <value>;
> update T set t2 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da =
> 2) as b where tkey = <value>;
> ...
> update T set t10 = b.aa FROM (select COUNT(1) as aa FROM D WHERE D.da =
> 10) as b where tkey = <value>;
You should be able to generate all the counts from one scan:
UPDATE T set t1 = b.a1, t2 = b.a2 ...
FROM ( SELECT sum(CASE WHEN D.da=1 THEN 1 ELSE 0 END) AS a1, sum(CASE WHEN D.da=2 THEN 1 ELSE 0 END) AS a2, ...
FROMD
) AS b
WHERE tkey = <value>
You might also want to look at the crosstab functions in the tablefunc
contrib module (see appendix F of the manuals).
-- Richard Huxton Archonet Ltd