MySQL: Set user variable from result of query


Is it possible to set an user variable based on the result of a query in MySQL?

What I want to achieve is something like this (we can assume that both USER and GROUP are unique):

set @user = 123456;
set @group = select GROUP from USER where User = @user;
select * from USER where GROUP = @group;

Please note that I know it's possible but I do not wish to do this with nested queries.

4/16/2012 1:24:20 PM

Accepted Answer

Yes, but you need to move the variable assignment into the query:

SET @user := 123456;
SELECT @group := `group` FROM user WHERE user = @user;
SELECT * FROM user WHERE `group` = @group;

Test case:

CREATE TABLE user (`user` int, `group` int);
INSERT INTO user VALUES (123456, 5);
INSERT INTO user VALUES (111111, 5);


SET @user := 123456;
SELECT @group := `group` FROM user WHERE user = @user;
SELECT * FROM user WHERE `group` = @group;

| user   | group |
| 123456 |     5 |
| 111111 |     5 |
2 rows in set (0.00 sec)

Note that for SET, either = or := can be used as the assignment operator. However inside other statements, the assignment operator must be := and not = because = is treated as a comparison operator in non-SET statements.


Further to comments below, you may also do the following:

SET @user := 123456;
SELECT `group` FROM user LIMIT 1 INTO @group; 
SELECT * FROM user WHERE `group` = @group;
10/8/2010 8:18:49 AM

Just add parenthesis around the query:

set @user = 123456;
set @group = (select GROUP from USER where User = @user);
select * from USER where GROUP = @group;

First lets take a look at how can we define a variable in mysql

To define a varible in mysql it should start with '@' like @{variable_name} and this '{variable_name}', we can replace it with our variable name.

Now, how to assign a value in a variable in mysql. For this we have many ways to do that

  1. Using keyword 'SET'.

Example :-

mysql >  SET @a = 1;
  1. Without using keyword 'SET' and using ':='.


mysql > @a:=1;
  1. By using 'SELECT' statement.


mysql > select 1 into @a;

Here @a is user defined variable and 1 is going to be assigned in @a.

Now how to get or select the value of @{variable_name}.

we can use select statement like

Example :-

mysql > select @a;

it will show the output and show the value of @a.

Now how to assign a value from a table in a variable.

For this we can use two statement like :-


@a := (select emp_name from employee where emp_id = 1);
select emp_name into @a from employee where emp_id = 1;

Always be careful emp_name must return single value otherwise it will throw you a error in this type statements.

refer this:-


Use this way so that result will not be displayed while running stored procedure.

The query:

SELECT a.strUserID FROM tblUsers a WHERE a.lngUserID = lngUserID LIMIT 1 INTO @strUserID;

Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]