update value in row based on value in another row

Question

I have a table with timestamped data.

t_stamp | col1 | col2 | col3

I want to update the value in say col3 based on values in col1 and col2 from the previous row in the table. By previous row I mean the row with the next lowest timestamp value. I also want to do this for every row in the table.

For example:

col3 = col1.prev + col2

I was able to use a window function to create a SELECT query to give me the desired values for col3

SELECT lag(col1) OVER (ORDER BY t_stamp ASC) + col2 AS col3
FROM table1

but this does not update the values in the table. Can I somehow apply this to the original table? Or is there a way to format an update query in the same way?

Answer 1

You just need to use the FROM clause along with the query you already have:

UPDATE test set col3 = prev_col1 + prev_col2
FROM (
  SELECT t_stamp,
      lag(col1) OVER (ORDER BY t_stamp ASC) prev_col1,
      lag(col2) OVER (ORDER BY t_stamp ASC) prev_col2
  FROM test) prev 
  WHERE prev.t_stamp = test.t_stamp;