mysql: update with subquery,
<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>