Updating a column with the results of a query in PostgreSQL
Solution 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;
Solution 2
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}}
Solution 3
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,
jatobat
Updated on June 07, 2022Comments
-
jatobat about 2 years
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, emptyI 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?
-
Clockwork-Muse over 11 yearsYes, this is the general form of what he needs to do... perhaps fill out the rest of the query?
-
Mark Amery over 11 years+1. This looks right to me, and beat me by 4 minutes. Never knew about the existence of
WITH... AS...
until now. Cool. :) -
a_horse_with_no_name over 11 years@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)
-
Spencer Kershaw over 8 yearsIf 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.
-
a_horse_with_no_name over 8 years@SpencerKershaw: well, the question is tagged
postgresql
so it's obvious this answer won't apply to SQL Server. -
Spencer Kershaw over 8 yearsYour 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.
-
Ambran about 8 yearsThis answer worked for me having a problem with PostgreSQL 9.0.1. Thanks.