Updating a column with the results of a query in PostgreSQL

Question

I have the following table in PostgreSQL 9.2 which contains time stamps:

gid [PK] (bigserial), timestamp_mes (timestamp without time zone), time_diff (interval)
1, 2012-01-23 11:03:40, empty
2, 2012-01-23 11:03:42, empty
3, 2012-01-23 11:03:44, empty

I have added a interval column (time_diff) and would like to fill it with time difference values resulting from this query:

SELECT timestamp_mes - lag(timestamp_mes, 1) 
over (order by timestamp_mes) as diff
from gc_entretien.trace order by timestamp_mes

I have tried the following query to update the time_diff column, with no success:

UPDATE gc_entretien.trace set time_diff = 
(SELECT trace.timestamp_mes - lag(trace.timestamp_mes, 1) 
over (order by trace.timestamp_mes) 
from gc_entretien.trace order by timestamp_mes);

This results in an error:

ERROR: more than one row returned by a subquery used as an expression

How should I proceed to update the time_diff column with the values resulting from the time difference query?

Answer 1

Something like this:

with new_values as (
   SELECT gid, 
          timestamp_mes - lag(timestamp_mes, 1) over (order by timestamp_mes) as diff
   from gc_entretien.trace 
)
update gc_entretien.trace as tr
  set time_diff = nv.diff
from new_values nv
where nv.gid = tr.gid;