Skip navigation links

Additional languages
12.8.3.4 Scope and Resolution of Local Variables »
Section Navigation      [Toggle]

12.8.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 Aurel Pekarcik on March 11 2006 10:50pm[Delete] [Edit]

When SELECT returns no rows, then the variables stay unchanged !
Dont you suppose that the variables will assigned to NULL.

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.

Posted by Daniel James on September 2 2008 6:21pm[Delete] [Edit]

[quote]
When SELECT returns no rows, then the variables stay unchanged !
Dont you suppose that the variables will assigned to NULL.
[/quote]

Actually neither is true as of MySQL 5.0.45. When the select statement returns no rows, the variables are emptied. This seems to be detectable only with the 'IS NULL' operator. This is the workaround I used:

SELECT col_name INTO var_name
FROM table_name
WHERE primary_key = row_id;

IF var_name IS NULL
THEN SET var_name := '';
END IF;

Posted by Serdar S. Kacar on February 13 2009 6:09pm[Delete] [Edit]

SELECT ... INTO Statement and Error: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)

http://bugs.mysql.com/bug.php?id=42834

Add your own comment.