(mysql.info) subquery-errors

(mysql.info) optimizing-subqueries
 
 13.2.8.9 Subquery Errors
 ........................
 
 There are some errors that apply only to subqueries. This section
 describes them.
 
    * Incorrect number of columns from subquery:
 
           ERROR 1241 (ER_OPERAND_COL)
           SQLSTATE = 21000
           Message = "Operand should contain 1 column(s)"
 
      This error occurs in cases like this:
 
           SELECT (SELECT column1, column2 FROM t2) FROM t1;
 
      You may use a subquery that returns multiple columns, if the
      purpose is comparison. See  row-subqueries. However, in
      other contexts, the subquery must be a scalar operand.
 
    * Incorrect number of rows from subquery:
 
           ERROR 1242 (ER_SUBSELECT_NO_1_ROW)
           SQLSTATE = 21000
           Message = "Subquery returns more than 1 row"
 
      This error occurs for statements where the subquery returns more
      than one row. Consider the following example:
 
           SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
 
      If `SELECT column1 FROM t2' returns just one row, the previous
      query will work. If the subquery returns more than one row, error
      1242 will occur. In that case, the query should be rewritten as:
 
           SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2);
 
    * Incorrectly used table in subquery:
 
           Error 1093 (ER_UPDATE_TABLE_USED)
           SQLSTATE = HY000
           Message = "You can't specify target table 'x'
           for update in FROM clause"
 
      This error occurs in cases such as the following:
 
           UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1);
 
      You can use a subquery for assignment within an `UPDATE' statement
      because subqueries are legal in `UPDATE' and `DELETE' statements
      as well as in `SELECT' statements. However, you cannot use the
      same table (in this case, table `t1') for both the subquery's
      `FROM' clause and the update target.
 
 For transactional storage engines, the failure of a subquery causes the
 entire statement to fail. For non-transactional storage engines, data
 modifications made before the error was encountered are preserved.
Info Catalog
(mysql.info) unnamed-views
(mysql.info) subqueries
(mysql.info) optimizing-subqueries
automatically generated byinfo2html