Re: update and group by/aggregate

Oops, both my statements were horribly broken. They needed a WHERE
clause for the UPDATE.

On Wed, Aug 27, 2008 at 11:44:20AM +0100, Sam Mason wrote:
> UPDATE catalog_items SET authors=array_to_string(x.authors,', ')
> FROM (
> SELECT ia.itemid, array_accum(trim(' \t]' from a.name)) AS authors
> FROM catalog_itemauthor ia
> JOIN catalog_author a ON a.authorid = ia.authorid
> WHERE a.name IS NOT NULL
> AND length(trim(' \t' from a.name))>1
> GROUP BY ia.itemid) x;

should be:

UPDATE catalog_items i SET authors=array_to_string(x.authors,', ')
FROM (
SELECT ia.itemid, array_accum(trim(' \t]' from a.name)) AS authors
FROM catalog_itemauthor ia
JOIN catalog_author a ON a.authorid = ia.authorid
WHERE a.name IS NOT NULL
AND length(trim(' \t' from a.name))>1
GROUP BY ia.itemid) x
WHERE i.itemid = x.itemid;

> UPDATE catalog_items SET authors=array_to_string(x.authors,', ')
> FROM (
> SELECT ia.itemid, array_accum(a.name) AS authors
> FROM catalog_itemauthor ia, (
> SELECT authorid, trim(' \t' from name) AS name
> FROM catalog_author) a
> WHERE ia.authorid = a.authorid
> AND a.name IS NOT NULL
> AND length(a.name) > 1
> GROUP BY ia.itemid) x;

should be:

UPDATE catalog_items i SET authors=array_to_string(x.authors,', ')
FROM (
SELECT ia.itemid, array_accum(a.name) AS authors
FROM catalog_itemauthor ia, (
SELECT authorid, trim(' \t' from name) AS name
FROM catalog_author) a
WHERE ia.authorid = a.authorid
AND a.name IS NOT NULL
AND length(a.name) > 1
GROUP BY ia.itemid) x
WHERE i.itemid = x.itemid;

Sorry!

Sam