Does PostgreSQL have a mechanism to update the same row multiple times in a single query?

Question

Consider the following:

create table tmp.x (i integer, t text);
create table tmp.y (i integer, t text);
delete from tmp.x;
delete from tmp.y;
insert into tmp.x values (1, 'hi');
insert into tmp.y values(1, 'there');
insert into tmp.y values(1, 'wow');

In the above, there is one row in table x, which I want to update. In table y, there are two rows, both of which I want to "feed data into" the update.

Below is my attempt:

update tmp.x
set t = x.t || y.t
from ( select * from tmp.y order by t desc ) y
where y.i = x.i;

select * from tmp.x;

I want the value of x.t to be 'hiwowthere' but the value ends up being 'hiwow'. I believe the cause of this is that the subquery in the update statement returns two rows (the y.t value of 'wow' being returned first), and the where clause y.i = x.i only matches the first row.

Can I achieve the desired outcome using a single update statement, and if so, how?

UPDATE: The use of the text type above was for illustration purposes only. I do not actually want to modify textual content, but rather JSON content using the json_set function that I posted here (How do I modify fields inside the new PostgreSQL JSON datatype?), although I'm hoping the principle could be applied to any function, such as the fictional concat_string(column_name, 'string-to-append').

UPDATE 2: Rather than waste time on this issue, I actually wrote a small function to accomplish it. However, it would still be nice to know if this is possible, and if so, how.

Answer 1

What you can do is to build up a concatenated string using string_agg, grouped by the integer i, which you can then join onto during the update:

update tmp.x
set t = x.t || y.txt
from (select i, string_agg(t, '') as txt
     from(
       select tmp.y.i,tmp.y.t
        from tmp.y
        order by t desc
      ) z
      group by z.i) y
where y.i = x.i ;

In order to preserve the order, you may need an additional wrapping derived table. SqlFiddle here

Answer 2

Use string_agg, as follows:

update tmp.x x
set t = x.t || (
                select string_agg(t,'' order by t desc) 
                from tmp.y where i = x.i 
                group by i
               )

SQLFiddle

Answer 3

with cte as (
select y.i, string_agg(t, '' order by t desc)  as txt
      from y
      group by y.i
)
update x set t= x.t||cte.txt 
from cte where cte.i=x.i