245

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.

4 Answers 4

394

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);

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;
4
  • 3
    Btw, could you suppress the output of the first statement (as its only variable assignment) and only show the output of the second query? Oct 8, 2010 at 8:15
  • 3
    @Avada: Updated my answer with an alternative, which doesn't output a result for the variable assignment. Oct 8, 2010 at 8:19
  • 1
    @DanielVassallo, There is also select grop into @group from user limit 1.
    – Pacerier
    Apr 1, 2015 at 13:17
  • @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! Aug 3, 2018 at 14:06
90

Just add parenthesis around the query:

set @user = 123456;
set @group = (select GROUP from USER where User = @user);
select * from USER where GROUP = @group;
2
  • 3
    This results in Subquery returns more than 1 row for me
    – timbroder
    Aug 15, 2019 at 20:31
  • 2
    @timbroder Just add LIMIT 1 to the query. Anyway, I suggested an edit to correct that.
    – Youkko
    Sep 6, 2019 at 0:34
26

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 ':='.

Example:-

mysql > @a:=1;
  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 :-

1.

@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

13

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;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.