CODEWITHSUNDEEP
postgresql
YVS1997
YVS1997

Reputation: 680

PostgreSQL update query with LAG() return "ERROR: more than one row returned by a subquery used as an expression"

I have table in my PostgreSQL database like below example. It has an empty column which needed to filled by previous row value

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    |             |

and it must filled like this:

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         |

I try with this query:

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

However it return error: "ERROR: more than one row returned by a subquery used as an expression" Any help are appreciated

Upvotes: 1

Views: 230

Answers (1)

user330315
user330315

Reputation:

Use a derived table that calculates all previous names once and join to that. As your table doesn't have a primary key (which is something you should really change), I used the internal "row identifier" from 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

Online example

Upvotes: 3

Related Questions