Shafizadeh Shafizadeh - 7 months ago 12
SQL Question

How to assign the result of a query into a variable?

I have this query:

SELECT col1, col2 FROM tablename WHERE id = 'n';


Note:
n
is unique and always the result of query above is one row.




Now I want to store
col1
and
col2
into a variable. Something like this:

SET @var_name = SELECT col1, col2 FROM tablename WHERE id = 'n';


As you see, there is two columns. So I want to know how can I get the value of those columns separately?




In reality I want to use that like this:

SET @var_name = SELECT col1, col2 FROM tablename WHERE id = 'n';

IF ( @var_name[col1] = 'x' AND @var_name[col2] = 'xx' ) THEN
-- do stuff
ELSEIF ( @var_name[col1] = 'y' AND @var_name[col2] = 'yy' ) THEN
-- do stuff
END IF;


But my syntax doesn't work. How can I fix it?

Answer

You would store it in two variables as:

SELECT @var1 := col1, @var2 := col2
FROM tablename
WHERE id = 'n';

You could get a string representation of the two values using some form of concatenation:

SELECT @var := CONCAT_WS(', ', col1, col2)
FROM tablename
WHERE id = 'n';

However, MySQL variables are not arrays, so you are limited to numeric and string types.