18

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?

jatobat
  • 749
  • 3
  • 8
  • 21
  • 1
    Not sure about the logic of your app, but the select returns possibly more rows which causes error when assigning into single column (like you do in your `UPDATE`)... if the result is in first row returned in your `SELECT`, use `LIMIT 1` to make the assignment possible. Anyway the select doesn't seem to be correctly designed. – Kamil Šrot Dec 07 '12 at 17:29
  • @KamilŠrot - There's a problem with that; because the subquery is (currently) uncorrelated, `LIMIT 1` will just return the top row, period, not one that has anything to do with the current row. – Clockwork-Muse Dec 07 '12 at 17:44
  • @Clockwork-Muse right, that's why I say it's not correctly designed query. First @jatobat need to create a query returning exactly one value (the one he needs according to the application logic) and then put it as subquery into `UPDATE` query... usual scenario is to use some identifiers/values from the table (resp. row) being updated in the subquery as condition(s) in the subquery `WHERE` clause. But frankly said: I don't understand the logic of the app and even don't wanna to understand it :-) – Kamil Šrot Dec 07 '12 at 17:52

3 Answers3

43

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;
  • +1. This looks right to me, and beat me by 4 minutes. Never knew about the existence of `WITH... AS...` until now. Cool. :) – Mark Amery Dec 07 '12 at 19:38
  • @MarkAmery: that's called a "common table expression", and since 9.1 this can also be used for DML statements (before that it could only be used for normal SELECT statements - including recursive queries) –  Dec 07 '12 at 19:39
  • If you are doing this in T-SQL then it seems you cannot alias the update table. If you use the table name explicitly (instead of tr in this case) it would work though. – Spencer Kershaw Dec 29 '15 at 21:12
  • 1
    @SpencerKershaw: well, the question is tagged `postgresql` so it's obvious this answer won't apply to SQL Server. –  Dec 29 '15 at 21:29
  • 1
    Your answer is extremely useful though for SQL in general, it was exactly what i was looking for. I just figured others may experience the same I did. – Spencer Kershaw Dec 31 '15 at 00:17
7

You can't directly use a window function in an UPDATE, so you instead need to use it in a sub-SELECT - which you have done. However, the way you've tried to use that sub-SELECT in your UPDATE is not valid syntax. You need to put the sub-SELECT in the FROM clause of your update, as explained by the Postgres docs here:

http://www.postgresql.org/docs/9.2/static/sql-update.html

The correct syntax for what you want to do is:

UPDATE gc_entretien.trace t
SET time_diff = subquery.diff
FROM (SELECT {{SomeUniqueId}}, 
             timestamp_mes - lag(timestamp_mes, 1) over (order by timestamp_mes) as diff
      FROM gc_entretien.trace order by timestamp_mes) AS subquery
WHERE t.{{SomeUniqueId}} = subquery.{{SomeUniqueId}}

Obviously, you'll need to substitute in the column name of some unique id that your rows have where I've written {{SomeUniqueId}}

Mark Amery
  • 143,130
  • 81
  • 406
  • 459
1

Actually you are getting this error because your subquery returns multiple result,

I am not able to understand your query so,

I will give you an example to solve it, 

update table t1 set time_diff= select *your_operation* from table t2 where t1.id=t2.id

Here :-your_operation means the logic of finding time difference,

Hunter
  • 820
  • 8
  • 19