Skip navigation links
12.7.3.4 Scope and Resolution of Local Variables »
Section Navigation      [Toggle]

12.7.3.3. SELECT ... INTO Statement

SELECT col_name [, col_name] ...
    INTO var_name [, var_name] ...
    table_expr

SELECT ... INTO syntax enables selected columns to be stored directly into variables. The query should return a single row. If the query returns no rows, a warning with error code 1329 occurs (No data), and the variable values remain unchanged. If the query returns multiple rows, error 1172 occurs (Result consisted of more than one row). If it is possible that the statement may retrieve multiple rows, you can use LIMIT 1 to limit the result set to a single row. 

SELECT id,data INTO x,y FROM test.t1 LIMIT 1;

User variable names are not case sensitive. See Section 8.4, “User-Defined Variables”.


User Comments

Posted by Ravi Krishnan on May 9 2008 8:29am[Delete] [Edit]

If you use table aliases, then you can get around the restriction of variable names to be different from referenced table column names, since you are avoiding ambiguity.

In the example above:

SELECT T.xname,id INTO newname,xid
FROM table1 T where ...
SELECT newname;


Returns the value of the xname column of table1.

Add your own comment.