CODEWITHSUNDEEP
postgresqlsql-updatelag
Luigi Falco
Luigi Falco

Reputation: 65

update table using LAG function

I have the st_12_test table.

CREATE TABLE public.st_12_test
(
  mmsi numeric,
  x1 double precision,
  y1 double precision,
  datetime timestamp without time zone,
  x2 double precision,
  y2 double precision,
  linetime double precision
)

I am trying to udapte the x2 column (which is empty) by adding the value of the previous row in the column x1. I am able to view the results by using a query like this:

SELECT  *, lag(x1,1) OVER (PARTITION BY mmsi) AS x2 FROM st_12_test;

but I am not able to update the x2 column of the table. I get this ERROR: cannot use window function in UPDATE, e.g. when I try to run something like this:

UPDATE st_12_test SET x2 = LAG(x1,1) OVER (PARTITION BY mmsi);

any idea?

Upvotes: 2

Views: 1238

Answers (1)

Belayer
Belayer

Reputation: 14936

You can create a CTE which uses the LAG function. Then use the resulting data in an Update...From. Assuming previous is defined based on the datetime column then: 

with ts12cte (mmsi, datetime, prev_x) as 
   ( select mmsi, datetime
          , lag(x1) over (partition by mmsi order by mmsi, datetime) 
       from st_12_test
   ) 
update st_12_test  st  
   set x2 = cte.prev_x   
  from ts12cte  cte
 where st.mmsi = cte.mmsi
   and st.datetime = cte.datetime;

Since you did not provide test data it has not been tested. It does however pass syntax validation.

Upvotes: 1

Related Questions