Re: Possible to UPDATE array[] columns?

[Sam Mason <sam(at)samason(dot)me(dot)uk>]

On Fri, Oct 30, 2009 at 10:47:26AM -0700, Blake Starkenburg wrote:
> ID  | scores
> 2   | {54,14,21,8}
> 3   | {12,0,7}
> 
> Now I want to append the score of 12 on row:ID 2 so the new scores would
> read {54,14,21,8,12}.

You need to use the normal array concatenation operator, ||, for
example:

  UPDATE table SET scores = scores || (
    SELECT scores FROM table WHERE id = 12)
  WHERE id = 2;

This pulls out the scores for id=12 and appends them onto all the scores
of the rows where id=2.  The reason for the sub-select is that any query
can only ever refer to the "current" row and not to any other row, the
way to get around this is to join tables together and then you are able
to compare every row with every other row.

-- 
  Sam  http://samason.me.uk/