title: Does PostgreSQL have a mechanism to update the same row multiple times in a single query?
body: <p>Consider the following:</p>
  
  <pre><code>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');
  </code></pre>
  
  <p>In the above, there is one row in table <code>x</code>, which I want to update. In table <code>y</code>, there are two rows, both of which I want to "feed data into" the update.</p>
  
  <p>Below is my attempt:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>I want the value of <code>x.t</code> to be <code>'hiwowthere'</code> but the value ends up being <code>'hiwow'</code>. I believe the cause of this is that the subquery in the update statement returns two rows (the <code>y.t</code> value of <code>'wow'</code> being returned first), and the <code>where</code> clause <code>y.i = x.i</code> only matches the first row.</p>
  
  <p>Can I achieve the desired outcome using a single <code>update</code> statement, and if so, how?</p>
  
  <p><strong>UPDATE:</strong> The use of the <code>text</code> type above was for illustration purposes only. I do not actually want to modify textual content, but rather JSON content using the <code>json_set</code> function that I posted here (<a href="https://stackoverflow.com/questions/18209625/how-do-i-modify-fields-inside-the-new-postgresql-json-datatype/28686182#28686182">How do I modify fields inside the new PostgreSQL JSON datatype?</a>), although I'm hoping the principle could be applied to any function, such as the fictional <code>concat_string(column_name, 'string-to-append')</code>.</p>
  
  <p><strong>UPDATE 2:</strong> 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.</p>