title: mysql: update with subquery,
body: <p>I have an update query with a select statement, which separately works. It's using it for the update that's not working.</p>
  
  <pre><code>update data set data.id = (select nid from node inner join data on node.title = data.name);
  </code></pre>
  
  <p>I get the error </p>
  
  <blockquote>
    <p>"You can't specify target table 'data' for update in FROM clause"</p>
  </blockquote>
  
  <p>So, after digging around, I found that I could write include another select statement: </p>
  
  <pre><code>update data set data.id = (select nid from(select nid from node inner join data on node.title = data.name) AS temptable);
  </code></pre>
  
  <p>I get the error </p>
  
  <blockquote>
    <p>"Subquery returns more than 1 row "</p>
  </blockquote>
  
  <p>So after more digging, I added an "ANY", as this is the common recommendation:</p>
  
  <pre><code>update data set data.id = (select nid from ANY (select nid from node inner join data on node.title = data.name) AS temptable);
  </code></pre>
  
  <p>and get </p>
  
  <blockquote>
    <p>"You have an error in your SQL syntax; check the manual that
    corresponds to your MySQL server version for the right syntax to use
    near '(select nid from node inner join data on node.title = biblio_'
    at line 1 "</p>
  </blockquote>
  
  <p>What am I missing? </p>