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 statement must retrieve only a single 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;

Add your own comment.