title: "no such column" on WITH UPDATE statement
body: <p>I'm looking directly at the <a href="http://www.sqlite.org/lang_update.html" rel="nofollow">sqlite documentation for the UPDATE statement</a>, and I have some SQL which I believe meets the syntax specified:</p>
  
  <pre><code>CREATE TEMPORARY TABLE x (y INTEGER); 
  INSERT into x SELECT value FROM table WHERE id IN (6,7,12,15) ORDER by value ASC; 
  CREATE TEMPORARY TABLE z (y INTEGER);
  INSERT INTO z (y) VALUES (6),(7),(12),(15);
  WITH NewValues(f,g) AS
      (SELECT a.y AS f,b.y AS g FROM 
          (SELECT rowid,y FROM x) AS a,
          (SELECT rowid,y FROM z) as b
       ON a.rowid==(1+b.rowid%4)
      )
  UPDATE table
  SET value = NewValues.g
  WHERE NewValues.f = table.id;
  </code></pre>
  
  <p>But when I try to execute this statement, I get the error:</p>
  
  <pre><code>near "WITH": syntax error
  </code></pre>
  
  <p>The code before the <code>WITH</code> is fine; if I just run the sub-select of the <code>WITH</code> and remove the remainder of the <code>UPDATE</code> it returns a table as I expect.  The syntax chart very clearly shows that I can put the <code>WITH</code> at the start of the statement.  So what am I doing wrong?</p>
  
  <p>Update:
  I have tried out one of the simple <a href="http://www.sqlite.org/lang_with.html" rel="nofollow">examples</a> given:</p>
  
  <pre><code>WITH RECURSIVE
      cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x&lt;1000000)
  SELECT x FROM cnt;
  </code></pre>
  
  <p>And I get a syntax error from that too!  My first thought was maybe I don't have a new enough version of sqlite or something, but I don't see any mentions of a minimum version that it was introduced in.</p>
  
  <p>Update 2:
  I downloaded the latest sqlite which is 3.8.5, whereas I was running 3.7.12.  This fixed the <code>WITH</code> syntax error, so it appears that it does indeed require a rather new version of Sqlite.  However I am still getting:</p>
  
  <pre><code>no such column: q.g
  </code></pre>
  
  <p>Which I don't understand in the context of the query, since I have defined it two ways.</p>