<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="subquery-errors">
      </a>
      15.2.15.10 Subquery Errors
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045179643472">
  </a>
  <a class="indexterm" name="idm46045179641984">
  </a>
  <p>
   There are some errors that apply only to subqueries. This
        section describes them.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Unsupported subquery syntax:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-none"><div class="docs-select-all right" id="sa73379320"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">ERROR 1235 (ER_NOT_SUPPORTED_YET)
SQLSTATE = 42000
Message = "This version of MySQL doesn't yet support
'LIMIT &amp; IN/ALL/ANY/SOME subquery'"</code></pre>
     </div>
     <p>
      This means that MySQL does not support statements like the
            following:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa63377028"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> s1 <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> s2 <span class="token keyword">FROM</span> t2 <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> s1 <span class="token keyword">LIMIT</span> <span class="token number">1</span><span class="token punctuation">)</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Incorrect number of columns from subquery:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-none"><div class="docs-select-all right" id="sa49968939"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">ERROR 1241 (ER_OPERAND_COL)
SQLSTATE = 21000
Message = "Operand should contain 1 column(s)"</code></pre>
     </div>
     <p>
      This error occurs in cases like this:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa78063251"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> column1<span class="token punctuation">,</span> column2 <span class="token keyword">FROM</span> t2<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      You may use a subquery that returns multiple columns, if the
            purpose is row comparison. In other contexts, the subquery
            must be a scalar operand. See
      <a class="xref" href="row-subqueries.html" title="15.2.15.5 Row Subqueries">
       Section 15.2.15.5, “Row Subqueries”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Incorrect number of rows from subquery:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-none"><div class="docs-select-all right" id="sa85943609"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">ERROR 1242 (ER_SUBSELECT_NO_1_ROW)
SQLSTATE = 21000
Message = "Subquery returns more than 1 row"</code></pre>
     </div>
     <p>
      This error occurs for statements where the subquery must
            return at most one row but returns multiple rows. Consider
            the following example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa72780581"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> column1 <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> column1 <span class="token keyword">FROM</span> t2<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      If
      <code class="literal">
       SELECT column1 FROM t2
      </code>
      returns just
            one row, the previous query works. If the subquery returns
            more than one row, error 1242 occurs. In that case, the
            query should be rewritten as:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa25949540"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> column1 <span class="token operator">=</span> <span class="token keyword">ANY</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> column1 <span class="token keyword">FROM</span> t2<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Incorrectly used table in subquery:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-none"><div class="docs-select-all right" id="sa1565898"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">Error 1093 (ER_UPDATE_TABLE_USED)
SQLSTATE = HY000
Message = "You can't specify target table 'x'
for update in FROM clause"</code></pre>
     </div>
     <p>
      This error occurs in cases such as the following, which
            attempts to modify a table and select from the same table in
            the subquery:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa38864519"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">UPDATE</span> t1 <span class="token keyword">SET</span> column2 <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token function">MAX</span><span class="token punctuation">(</span>column1<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      You can use a common table expression or derived table to
            work around this. See
      <a class="xref" href="subquery-restrictions.html" title="15.2.15.12 Restrictions on Subqueries">
       Section 15.2.15.12, “Restrictions on Subqueries”
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   All of the errors described in this section also apply when
        using
   <a class="link" href="table.html" title="15.2.16 TABLE Statement">
    <code class="literal">
     TABLE
    </code>
   </a>
   in subqueries.
  </p>
  <p>
   For transactional storage engines, the failure of a subquery
        causes the entire statement to fail. For nontransactional
        storage engines, data modifications made before the error was
        encountered are preserved.
  </p>
 </div>
 <br/>
</div>
