PostgreSQL是否有一种机制可以在单个查询中多次更新同一行？

Question

考虑以下内容：

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');

在上述情况中，表格x中有一行需要更新。在表格y中，有两行数据需要"注入"到更新中。以下是我的尝试：

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;

我希望x.t的值为'hiwowthere'，但实际上它的值是'hiwow'。我认为原因是更新语句中的子查询返回了两行（先返回'wow'的y.t值），而where子句y.i = x.i仅匹配了第一行。

是否可以使用单个update语句来实现所需的结果，如果可以的话，该如何实现？ 更新：上述使用text类型仅用于说明目的。我实际上不想修改文本内容，而是使用我在此处发布的json_set函数（如何修改新的PostgreSQL JSON数据类型内部的字段？）修改JSON内容，尽管我希望这个原则也适用于任何函数，例如虚构的concat_string(column_name,'string-to-append')。 更新2：我已经编写了一个小函数来解决此问题，以免浪费时间。然而，如果可能的话，知道是否可以实现以及如何实现仍然很好。

- magnus

Answer 1

你可以使用 string_agg 函数构建一个连接的字符串，按整数 i 分组，然后在更新期间加入到其中。

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 ;

为了保持顺序，您可能需要一个额外的包装衍生表。 这里是SqlFiddle。

- StuartLC

---

可以在 string_agg 中指定排序方式 - 请参考Grisha的回答。 - StuartLC

Answer 2

请使用 string_agg 函数，如下所示：

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

{{链接1：SQLFiddle}}

- Grisha Weintraub

---

不错！- 关于聚合选项的参考。 - StuartLC

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

- Vivek S.