使用 PostgreSQL 中的查询结果更新列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13767908/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Updating a column with the results of a query in PostgreSQL
提问by jatobat
I have the following table in PostgreSQL 9.2 which contains time stamps:
我在 PostgreSQL 9.2 中有下表，其中包含时间戳：
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
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, 空
I have added a interval column (time_diff) and would like to fill it with time difference values resulting from this query:
我添加了一个间隔列 (time_diff) 并想用此查询产生的时间差值填充它：
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:
我尝试了以下查询来更新 time_diff 列，但没有成功：
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?
我应该如何使用时差查询产生的值来更新 time_diff 列？
回答by a_horse_with_no_name
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;
回答by Mark Amery
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:
您不能在 UPDATE 中直接使用窗口函数，因此您需要在子 SELECT 中使用它 - 您已经完成了。但是，您尝试在 UPDATE 中使用该子 SELECT 的方式是无效的语法。您需要将 sub-SELECT 放在更新的 FROM 子句中，如此处的 Postgres 文档所述：
http://www.postgresql.org/docs/9.2/static/sql-update.html
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}}
显然，您需要用我写过的行的某些唯一 ID 的列名替换 {{SomeUniqueId}}
回答by Hunter
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,
这里:-your_operation 是指查找时差的逻辑，
