Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Is it possible to set a 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.
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);
Result:
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.
UPDATE:
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;
                Btw, could you suppress the output of the first statement (as its only variable assignment) and only show the output of the second query?
– Avada Kedavra
                Oct 8, 2010 at 8:15
                @Avada: Updated my answer with an alternative, which doesn't output a result for the variable assignment.
– Daniel Vassallo
                Oct 8, 2010 at 8:19
                @DanielVassallo thank you so much. The statment "INTO" is very helpfull. I was looking for setting mysql var from select without returning the select. THANKS!
– Luis Antonio Pestana
                Aug 3, 2018 at 14:06
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
Using keyword 'SET'.
Example :-
mysql >  SET @a = 1;
Without using keyword 'SET' and using ':='.
Example:-
mysql > @a:=1;
By using 'SELECT' statement.
Example:-
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:-
http://www.easysolutionweb.com/sql-pl-sql/how-to-assign-a-value-in-a-variable-in-mysql
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;
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.