CODEWITHSUNDEEP
sqlpostgresqlsql-updatepostgresql-9.6
iamthebull
iamthebull

Reputation: 109

update value in row based on value in another row

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

Note: The operation here is only provided as an example. I want to calculate a value for col3 given a function of col1, col2 and/or previous values of either.

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?

Upvotes: 0

Views: 2047

Answers (4)

Jeremy
Jeremy

Reputation: 6723

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;

Upvotes: 2

Ganesh Chandrasekaran
Ganesh Chandrasekaran

Reputation: 1936

Use Sub queries.. will solve.. here is a sample

select col2+p_col1 from
(
SELECT col1, col2, lag(col1) OVER (ORDER BY t_stamp ASC) as p_col1
FROM table1
) t

Upvotes: 0

Arundeep Chohan
Arundeep Chohan

Reputation: 9969

Create the first 3 columns and than use Lag to create the last col.

How to get previous row data in sql server

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

I think you want a cumulative sum:

SELECT (sum(col2 + co1) OVER (ORDER BY t_stamp ASC) - col1) AS col3
FROM table1

Upvotes: 0

Related Questions