带有 LAG() 的 PostgreSQL 更新查询返回"ERROR: more than one row returned by a subquery used as an expression"

Question

我的PostgreSQL数据库中有一个表，如下例所示。它有一个空列，需要由前一行值填充

ship_id|ship_class     |bow_number|name            |date_start_ops|previous_name|
-------|---------------|----------|----------------|--------------|-------------|
010    |Albatross Class|       010|Albatross       |1980-01-05    |             |
010    |Albatross Class|      909X|AP010           |1979-12-25    |             |
012    |Jaguar    Class|       010|Jaguar          |1978-11-05    |             |
012    |Jaguar    Class|     8091X|JX010           |1970-05-10    |             |

必须这样填写：

ship_id|ship_class     |bow_number|name            |date_start_ops|previous_name|
-------|---------------|----------|----------------|--------------|-------------|
010    |Albatross Class|       010|Albatross       |1980-01-05    |AP101        |
010    |Albatross Class|      909X|AP010           |1979-12-25    |NULL         |
012    |Jaguar    Class|       010|Jaguar          |1978-11-05    |JX010        |
012    |Jaguar    Class|     8091X|JX010           |1970-05-10    |NULL         |

我尝试使用这个查询：

update
historic as a
set
previous_name =
(select
lag(name, 1) over (partition by ship_id, ship_class
order by
date_start_ops)
from
historic
)
where
a.ship_id = ship_id

然而，它返回错误："；错误：用作表达式"的子查询返回了多行；感谢提供的任何帮助

Answer 1

使用一个派生表，该表一次性计算所有以前的名称并连接到该名称。由于您的表没有主键(这是您真正应该更改的内容(，我使用了内部"；行标识符"；来自Postgres:ctid

update historic as a
set previous_name = t.prev_name
from (
select ctid as row_id, 
lag(name, 1) over (partition by ship_id, ship_class 
order by date_start_ops) as prev_name
from historic
) t
where a.ctid = t.row_id 

在线示例