Update with result from cte (postgresql)

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.

TIA,

Answer 1

Try your UPDATE with the following syntax;

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

Answer 2

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