使用 cte (postgresql) 的结果更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36908495/
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
Update with result from cte (postgresql)
提问by sibert
I want to update job date if any records is an earlier date. Trying to use CTE to achieve this:
如果任何记录是更早的日期，我想更新工作日期。尝试使用 CTE 来实现这一点：
CREATE TABLE job
(jobid int4, jobdate date);
INSERT INTO job
(jobid, jobdate)
VALUES
(1, '2016-02-01'),
(2, '2016-02-01'),
(3, '2016-02-01'),
(4, '2016-02-01')
;
CREATE TABLE rec
(recid int4, recjob int4, recdate date);
INSERT INTO rec
(recid, recjob, recdate)
VALUES
(1,1,'2016-02-01'),
(2,2,'2016-01-01'),
(3,3,'2016-02-01'),
(4,4,'2016-02-01')
;
Job number 2 have a record dated earlier than the job date. So I want to update this job with the record date.
作业编号 2 的记录日期早于作业日期。所以我想用记录日期更新这个工作。
WITH cte AS
(SELECT jobid,least(min(recdate),jobdate)
FROM job
LEFT JOIN rec ON recjob=jobid
GROUP BY jobid,jobdate
HAVING least(min(recdate),jobdate)<jobdate)
Selecting the cte shows correct that job 2 should be updated
选择 cte 显示应更新作业 2 是正确的
SELECT * FROM cte
But updating gives an error: missing FROM-clause entry for table "cte"
但是更新会出现错误：缺少表“cte”的 FROM 子句条目
UPDATE job
SET jobdate=cte.date
WHERE jobid IN (SELECT jobid FROM cte)
SQLFiddle: http://sqlfiddle.com/#!15/e9ae6/8
SQLFiddle：http://sqlfiddle.com/#!15/e9ae6/8
I have never used cte with update, so I need some help to understand this.
我从未将 cte 与更新一起使用，所以我需要一些帮助来理解这一点。
TIA,
TIA,
回答by William
Try your UPDATEwith the following syntax;
尝试UPDATE使用以下语法；
UPDATE job
SET jobdate = cte.date
FROM cte
WHERE job.jobid = cte.jobid
回答by simPod
The syntax is as follows:
语法如下：
WITH cte AS (
SELECT * FROM ...
)
UPDATE table_to_update
SET column_from_table_to_update = cte.some_column
FROM cte
WHERE table_to_update.id = cte.id
回答by firetonton
A cte is a table. One solution is to do a select on it like this :
cte 是一张桌子。一种解决方案是像这样对其进行选择：
WITH cte AS
(SELECT jobid,least(min(recdate),jobdate)as date
FROM job
LEFT JOIN rec ON recjob=jobid
GROUP BY jobid,jobdate
HAVING least(min(recdate),jobdate)<jobdate)
UPDATE job
SET jobdate=(SELECT date from cte)
WHERE jobid IN (SELECT jobid from cte)
Or use an UPDATE ... FROM syntax
或者使用 UPDATE ... FROM 语法
