title: Update with result from cte (postgresql)
body: <p>I want to update job date if any records is an earlier date. Trying to use CTE to achieve this:</p>
  
  <pre><code>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')
  ;
  </code></pre>
  
  <p>Job number 2 have a record dated earlier than the job date. So I want to update this job with the record date.</p>
  
  <pre><code>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)&lt;jobdate)
  </code></pre>
  
  <p>Selecting the cte shows correct that job 2 should be updated</p>
  
  <pre><code>SELECT * FROM cte
  </code></pre>
  
  <p>But updating gives an error: missing FROM-clause entry for table "cte"</p>
  
  <pre><code>UPDATE job 
  SET    jobdate=cte.date 
  WHERE  jobid IN (SELECT jobid FROM cte)
  </code></pre>
  
  <p>SQLFiddle:  <a href="http://sqlfiddle.com/#!15/e9ae6/8" rel="noreferrer">http://sqlfiddle.com/#!15/e9ae6/8</a></p>
  
  <p>I have never used cte with update, so I need some help to understand this.</p>
  
  <p>TIA,</p>