0

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?

0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.