Update with result from CTE

Question

I want to update job date if any records is an earlier date. Trying to use CTE to achieve this:

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.

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

SELECT * FROM cte

But updating gives an error: missing FROM-clause entry for table "cte"

UPDATE job 
SET    jobdate=cte.date 
WHERE  jobid IN (SELECT jobid FROM cte)

SQLFiddle: http://sqlfiddle.com/#!15/e9ae6/8

I have never used CTE with UPDATE, so I need some help to understand this.

score 88 · Accepted Answer · answered Apr 28 '16 at 08:07

88

Try your UPDATE with the following syntax;

UPDATE job
SET jobdate = cte.date
FROM cte
WHERE job.jobid = cte.jobid

answered Apr 28 '16 at 08:07

William

6,332
8
38
57

1

Please note that sometimes it's worth trying it with a CTE, as explained in the answer below by simPod. On some (big) tables, using one over the other can yield great performance benefits. – Jasper Kuperus Aug 16 '22 at 09:22

score 61 · Answer 2 · answered May 26 '18 at 14:36

61

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

answered May 26 '18 at 14:36

simPod

11,498
17
86
139

Update with result from CTE

2 Answers2

Linked